Between Dates Parameter Query

  • Thread starter Thread starter Linda RQ
  • Start date Start date
L

Linda RQ

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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().
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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])
Click to expand...
 
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:
Click to expand...

Well... only if you don't want the time portion for some other purpose!!!
 
Well... only if you don't want the time portion for some other
purpose!!!
Click to expand...

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.
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...

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 said:
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.
Click to expand...

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.
Click to expand...

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])
 
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])
Click to expand...

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
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])
Click to expand...
 
Back
Top