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 |
#1
|
|||
|
|||
Between Dates Parameter Query
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 |
#2
|
|||
|
|||
Between Dates Parameter Query
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 |
#3
|
|||
|
|||
Between Dates Parameter Query
Thanks John. Right before lunch I was reading through the earlier posts and
found this in the Previous Month query post right below mine and saw that in his expression and thought...I'm gonna try that after lunch but now I know it will work. 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 |
#4
|
|||
|
|||
Between Dates Parameter Query
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 |
#5
|
|||
|
|||
Between Dates Parameter Query
On Fri, 16 Apr 2010 12:07:57 -0400, 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 Your Date Field includes a Time value. Therefore any data past 00:00:00 AM of the [EndDate] is not within the Between [FromDate] And [EndDate] criteria. Easiest work-around is to just add one day manually to the [EndDate] when prompted, i.e. instead of entering 3/31/2010 enter 4/1/2010. A better work-around would be to set [FromDate] and [EndDate] and their expected Datatype as query parameters in the Query Parameter Dialog (Click Query + Parameter). Then change your criteria to: Between [FromDate] and ([EndDate] + 1) The best solution, if the Time of day is not necessary, is to run an Update Query to remove the time value from the date: Update YourTable Set YourTable.[DateField] = DateValue([DateField]); Then make sure the time is not included in any new entries, i.e. use Date() instead of Now(). -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Between Dates Parameter Query
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 |
#7
|
|||
|
|||
Between Dates Parameter Query
On Fri, 16 Apr 2010 15:50:29 -0400, "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 Its once in a blue moon that John Spencer makes a mistake, but he had a single character typo; instead of =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) it should be =[Enter Start Date] and DateAdd(d,1,[Enter end Date]) -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Between Dates Parameter Query
On Fri, 16 Apr 2010 13:12:02 -0700, fredg wrote:
The best solution, if the Time of day is not necessary, is to run an Update Query to remove the time value from the date: Well... only if you don't want the time portion for some other purpose!!! -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Between Dates Parameter Query
John W. Vinson wrote in
: On Fri, 16 Apr 2010 13:12:02 -0700, fredg wrote: The best solution, if the Time of day is not necessary, is to run an Update Query to remove the time value from the date: Well... only if you don't want the time portion for some other purpose!!! I long ago changed to never store date and time in the same field. It solved a whole host of problems. On the other hand, had I ever needed the combined value to be indexed, it would have been a problem. But I think the default of never storing them in the same field is the best starting point, until you have a good reason to do otherwise. My bet is that most date fields in Access apps don't need a time part at all. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#10
|
|||
|
|||
Between Dates Parameter Query
"John W. Vinson" wrote in message ... On Fri, 16 Apr 2010 13:12:02 -0700, fredg wrote: The best solution, if the Time of day is not necessary, is to run an Update Query to remove the time value from the date: Well... only if you don't want the time portion for some other purpose!!! -- John W. Vinson [MVP] Yep....I need that time field in there. |
|
Thread Tools | |
Display Modes | |
|
|