If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Between Dates Parameter Query
Thanks John. Error is gone but I am getting more records than I want. I
enter 3/1/10 for start date and 3/31/10 for end date but as I read down...maybe having the time in there is causing the problem and since this query is only for one person to use...It is probably better to educate him on adding a day to the end of the month and if I ever become a "real" programmer I'll know how to fix this issue. "John Spencer" wrote in message ... My error I forgot QUOTE marks around the d Between [Enter Start Date] and DateAdd("d",1,[Enter end Date]) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Linda RQ wrote: Ok...tried both. 1st one give me a syntax error..."You may have entered an operand without an operator" 2nd one doesn't give me anything close If I add your second example I get 704 records with several years worth of records so something is filtered but I can't tell what If there is no criteria I get 742 records with several years worth of records Thanks, Linda "John Spencer" wrote in message ... Between [Enter Start Date] and DateAdd(d,1,[Enter end Date]) Better would be =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) The first would catch any record with the field value of Midnight On the End Date Plus 1. The second would only go up to but not include midnight on the second date plus one day. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Linda RQ wrote: Hi Everyone, Access 2003. I have a query that I have set up so the user can type in "between" dates. They pull data for the month so the first date they enter is 3/1/10 and the end date is 3/31/10. I want all records between these dates but anything that is 3/31 doesn't show up inless I type in 4/1. Is there a way I can add something in the expression so it automatically adds the one day? Thanks, Linda |
#12
|
|||
|
|||
Between Dates Parameter Query
On Mon, 19 Apr 2010 11:59:18 -0400, "Linda RQ"
wrote: Thanks John. Error is gone but I am getting more records than I want. I enter 3/1/10 for start date and 3/31/10 for end date but as I read down...maybe having the time in there is causing the problem and since this query is only for one person to use...It is probably better to educate him on adding a day to the end of the month and if I ever become a "real" programmer I'll know how to fix this issue. The criterion John suggests - Between [Enter Start Date] and DateAdd("d",1,[Enter end Date]) will correctly handle the end date. It will return records starting at 00:00am 3/1/10 and up through and including 12:59:59pm 3/31 if you enter 3/1/10 and 3/31/10 in the criteria. If that's not what you're seeing, *fix the query* rather than imposing on your user! This isn't rocket science... post the complete SQL view of the query and indicate what incorrect data you're seeing fi you would like help. -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Between Dates Parameter Query
"John W. Vinson" wrote in message ... On Mon, 19 Apr 2010 11:59:18 -0400, "Linda RQ" wrote: Thanks John. Error is gone but I am getting more records than I want. I enter 3/1/10 for start date and 3/31/10 for end date but as I read down...maybe having the time in there is causing the problem and since this query is only for one person to use...It is probably better to educate him on adding a day to the end of the month and if I ever become a "real" programmer I'll know how to fix this issue. The criterion John suggests - Between [Enter Start Date] and DateAdd("d",1,[Enter end Date]) will correctly handle the end date. It will return records starting at 00:00am 3/1/10 and up through and including 12:59:59pm 3/31 if you enter 3/1/10 and 3/31/10 in the criteria. If that's not what you're seeing, *fix the query* rather than imposing on your user! This isn't rocket science... post the complete SQL view of the query and indicate what incorrect data you're seeing fi you would like help. -- John W. Vinson [MVP] Well, since I don't need to be a rocket scientist I gave it another try. Thanks for encouraging me to do the right thing... the Between query works. It was when I used the one below, I got all the records displayed. I have several other queries that I run that I have to add that one day and I just fixed them now so I'm doing the right thing. Better would be =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) |
#14
|
|||
|
|||
Between Dates Parameter Query
That was because of a slight error on my part when I typed that expression.
=[Enter Start Date] and DateAdd(d,1,[Enter end Date]) SHOULD HAVE BEEN =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) Notice the change in the first comparison operator from Less Than Equal to GREATER than equal. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Linda RQ wrote: SNIP -- John W. Vinson [MVP] Well, since I don't need to be a rocket scientist I gave it another try. Thanks for encouraging me to do the right thing... the Between query works. It was when I used the one below, I got all the records displayed. I have several other queries that I run that I have to add that one day and I just fixed them now so I'm doing the right thing. Better would be =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) |
|
Thread Tools | |
Display Modes | |
|
|