Querying between two dates doesn't work if Start and End are ident

  • Thread starter Thread starter Richard Horne
  • Start date Start date
R

Richard Horne

Hello guys.

I'm trying to run a very simple query that shows all results from a table
called EE_DR, where the Date field in this table is between two certain
dates. I have a form called Snapshot that has two text boxes StartDate and
EndDate that populate the query.

The query is as follows:

SELECT * FROM EE_DR
WHERE (((EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));

The problem I'm having is that is if I put today's date as the start and the
end date, even though there should be 1 result for today, no results are
actually found.

If I change the range of dates so that it queries from yesterday to
tomorrow, today's result is found just fine.

The date field used in the query is formatted as a short date in UK format
dd/mm/yyy.

Any reason why this is happening?
 
--
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
You probably need to include the time in the comparison. The date stored in
the tab e probably has a time in it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Richard said:
Hello guys.

I'm trying to run a very simple query that shows all results from a
table called EE_DR, where the Date field in this table is between two
certain dates. I have a form called Snapshot that has two text boxes
StartDate and EndDate that populate the query.
Click to expand...

I understand what you are trying to say, but to be correct, these textboxes
are supplying criteria that are filtering the query results. "Populate" is
more commonly used to describe the process of entering data into a table.
The query is as follows:

SELECT * FROM EE_DR
WHERE (((EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));

The problem I'm having is that is if I put today's date as the start
and the end date, even though there should be 1 result for today, no
results are actually found.

If I change the range of dates so that it queries from yesterday to
tomorrow, today's result is found just fine.

The date field used in the query is formatted as a short date in UK
format dd/mm/yyy.

Any reason why this is happening?
Click to expand...

My initial thought was that times were entered into EE_DR.Date *.
Date/Time fields always store both date and time. The Format property is
irrelevant: it only controls how data is displayed. Date/Times are stored as
Double numbers, with the whole number portion representing the number of
days since the seed date, and the decimal representing the time of day (0 =
midnight and .5 = noon). So, if you enter today's date, without specifying
the time, the query is understanding you to have entered midnight. If the
value in EE_DR.Date is ont midnight, then it will get excluded from the
result. You can verify if that is the problem by running this query:
select Date] from EE_DR
where CInt([Date]) <> [Date]

My second thought (and the one I now think is probably the correct answer)
is you are entering dates in UK format. Date literals must be supplied to
queries in either US format (mm/dd/yyy), or, preferably, ISO format
(yyyy-mm-dd). You can confirm this easily enough. I would suggest you use a
calendar control to solicit date values from your users, or at least add
text to your form instructing them to enter them in ISO format.

* "Date" is a horrible name for a field. It is a reserved keyword since it
is the name of a VBA function. Someday you will run into an error such as
"unrecognized function: Date()" which is caused by the use of that word as a
field or table name. I'm a little surprised you are getting away without
putting brackets around the field name in your sql statement above.
Typically, the use of a reserved keyword in a query will raise an error
unless you delimit it with brackets as I have done in my example above. The
other issue is that "Date" is very non-descriptive: date of what? Adding
more description to the name (BirthDate,TransactionDate, ModificationDate,
etc.) will help you avoid using reserved keywords, a list of which can be
found here:
http://www.aspfaq.com/show.asp?id=2080
 
Dorian - I think you're onto something there.

If I put 00:00 and 23:59 on the end of my Start and End dates then the
expected results show.

Any ideas how I can either stop this behavious, or automatically bolt the
correct times onto the end of my start and end date variables?
 
Try this --
SELECT * FROM EE_DR
WHERE ((DateValue(EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));
 
Richard said:
Dorian - I think you're onto something there.

If I put 00:00 and 23:59 on the end of my Start and End dates then the
expected results show.

Any ideas how I can either stop this behavious, or automatically bolt
the correct times onto the end of my start and end date variables?
Click to expand...

You can change your criterion to either this:
WHERE EE_DR.[Date] >= [Forms].[snapshot].[StartDate] And
EE_DR.[Date] < [Forms].[Snapshot].[EndDate] + 1

or this:

WHERE EE_DR.[Date] BETWEEN [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate] + 0.999
 
Karl that worked a treat!

What a legend! Thanks mate.

KARL DEWEY said:
Try this --
SELECT * FROM EE_DR
WHERE ((DateValue(EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));

--
Build a little, test a little.


Richard Horne said:
Dorian - I think you're onto something there.

If I put 00:00 and 23:59 on the end of my Start and End dates then the
expected results show.

Any ideas how I can either stop this behavious, or automatically bolt the
correct times onto the end of my start and end date variables?
Click to expand...
Click to expand...
 
Thanks for your reply Bob.

Yep, you're right on my mis-use of the specific work populate and I'll
remember that in the future.

I also concede your point about naming a field Date - stupid move and one I
made when I first learning about Access and Databases full stop. As ever with
projects that spiral out of hand like mine inevitably has, mistakes made
early on are often very difficult to resolve after the fact. Particularly
when you have numerous reports, queries and forms picking up the data.

Luckily I'm about to rebuild our entire database so won't be making that
mistake again.

As it happens I don't think the date being formatted according to a UK style
was the problem. Karl's suggestion of adding DateValue solved it in the
simplest way possible.

Thanks all.



Bob Barrows said:
Richard said:
Hello guys.

I'm trying to run a very simple query that shows all results from a
table called EE_DR, where the Date field in this table is between two
certain dates. I have a form called Snapshot that has two text boxes
StartDate and EndDate that populate the query.
Click to expand...

I understand what you are trying to say, but to be correct, these textboxes
are supplying criteria that are filtering the query results. "Populate" is
more commonly used to describe the process of entering data into a table.
The query is as follows:

SELECT * FROM EE_DR
WHERE (((EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));

The problem I'm having is that is if I put today's date as the start
and the end date, even though there should be 1 result for today, no
results are actually found.

If I change the range of dates so that it queries from yesterday to
tomorrow, today's result is found just fine.

The date field used in the query is formatted as a short date in UK
format dd/mm/yyy.

Any reason why this is happening?
Click to expand...

My initial thought was that times were entered into EE_DR.Date *.
Date/Time fields always store both date and time. The Format property is
irrelevant: it only controls how data is displayed. Date/Times are stored as
Double numbers, with the whole number portion representing the number of
days since the seed date, and the decimal representing the time of day (0 =
midnight and .5 = noon). So, if you enter today's date, without specifying
the time, the query is understanding you to have entered midnight. If the
value in EE_DR.Date is ont midnight, then it will get excluded from the
result. You can verify if that is the problem by running this query:
select Date] from EE_DR
where CInt([Date]) <> [Date]

My second thought (and the one I now think is probably the correct answer)
is you are entering dates in UK format. Date literals must be supplied to
queries in either US format (mm/dd/yyy), or, preferably, ISO format
(yyyy-mm-dd). You can confirm this easily enough. I would suggest you use a
calendar control to solicit date values from your users, or at least add
text to your form instructing them to enter them in ISO format.

* "Date" is a horrible name for a field. It is a reserved keyword since it
is the name of a VBA function. Someday you will run into an error such as
"unrecognized function: Date()" which is caused by the use of that word as a
field or table name. I'm a little surprised you are getting away without
putting brackets around the field name in your sql statement above.
Typically, the use of a reserved keyword in a query will raise an error
unless you delimit it with brackets as I have done in my example above. The
other issue is that "Date" is very non-descriptive: date of what? Adding
more description to the name (BirthDate,TransactionDate, ModificationDate,
etc.) will help you avoid using reserved keywords, a list of which can be
found here:
http://www.aspfaq.com/show.asp?id=2080
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.
Click to expand...
 
Richard said:
Thanks for your reply Bob.

Yep, you're right on my mis-use of the specific work populate and I'll
remember that in the future.

I also concede your point about naming a field Date - stupid move and
one I made when I first learning about Access and Databases full
stop. As ever with projects that spiral out of hand like mine
inevitably has, mistakes made early on are often very difficult to
resolve after the fact. Particularly when you have numerous reports,
queries and forms picking up the data.

Luckily I'm about to rebuild our entire database so won't be making
that mistake again.

As it happens I don't think the date being formatted according to a
UK style was the problem. Karl's suggestion of adding DateValue
solved it in the simplest way possible.
Click to expand...
The problem with that solution is it may have impaired performance by making
your criterion "nonsargable". If you have an index on that date field, using
DateValue in that manner will prevent that index from being used. Using
either of my solutions is just as simple, and they have the added benefit of
allowing an index to be used. Again:

WHERE EE_DR.[Date] >= [Forms].[snapshot].[StartDate] And
EE_DR.[Date] < [Forms].[Snapshot].[EndDate] + 1

or this:

WHERE EE_DR.[Date] BETWEEN [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate] + 0.999
 
Hello guys.

I'm trying to run a very simple query that shows all results from a table
called EE_DR, where the Date field in this table is between two certain
dates. I have a form called Snapshot that has two text boxes StartDate and
EndDate that populate the query.

The query is as follows:

SELECT * FROM EE_DR
WHERE (((EE_DR.Date) Between [Forms].[snapshot].[StartDate] And
[Forms].[Snapshot].[EndDate]));

The problem I'm having is that is if I put today's date as the start and the
end date, even though there should be 1 result for today, no results are
actually found.

If I change the range of dates so that it queries from yesterday to
tomorrow, today's result is found just fine.

The date field used in the query is formatted as a short date in UK format
dd/mm/yyy.

Any reason why this is happening?
Click to expand...

The problem is that if your range is from #11/1/2009# to #11/23/2009#, that
range stops at midnight last night - and #11/23/2009 10:14:45# is LATER than
that end time.

Try

PARAMETERS [Forms].[snapshot].[StartDate] DateTime,
[Forms].[snapshot].[EndDate] DateTime;
SELECT * FROM EE_DR
WHERE (((EE_DR.Date) >= [Forms].[snapshot].[StartDate] And
(EE_DR.Date) < DateAdd("d", 1,[Forms].[Snapshot].[EndDate])));

to a) be sure that the criteria are parsed as dates and b) catch the records
from the last day of the range.
 
Back
Top