Criteria Compare dates using between and dates from other years are included

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DW - 15 Apr 2006 05:50 GMT

I have a query in Access 2003 that has the following criteria

SELECT tblOrder.SessionDate, tblMenus.Item_Name, tblOrder.Type,
Format$(tblorder!SessionDate,"Short Time") AS SessionTime,
Sum(tblMenus.Item_Quantity) AS MenuCount INTO tblSessionQuery
FROM tblOrder, tblMenus
WHERE (((tblOrder.Order_ID)=[tblMenus].[Order_ID]) AND
((Format$([tblOrder]![SessionDate],"mm/dd/yyyy")) Between
Format$([Forms]![frmReports]![txtBeginDate],"mm/dd/yyyy") And
Format$([Forms]![frmReports]![txtEndDate],"mm/dd/yyyy")))
GROUP BY tblOrder.SessionDate, tblMenus.Item_Name, tblOrder.Type,
Format$(tblorder!SessionDate,"Short Time")
ORDER BY tblOrder.SessionDate, tblMenus.Item_Name;

If I enter the same date for both txtbegindate and txtenddate, for
example 4/6/2006 , the correct records are returned. If I enter
4/1/2006 for txtBeginDate and 4/30/2006 for txtEndDate, records that
fall within 4/1/2005 to 4/30/2005 are returned along with the 4/1/2006
- 4/30/2006 records.
I have no idea why the records from 2005 are returned. I've tried at
least dozen changes to the criteria and I can't stop the 2005 records
from being returned. I use the Format$ because without it when I enter
the same date for begin/end no records are returned. The date fields
in the table are defined using datetime data type. I've tried
identifying the query parameters in the query properties but to no
affect.

The tables are not linked. They are Access tables, not SQL server
tables.

Any suggestions are appreciated.

DW

Reply to this Message

Allen Browne - 15 Apr 2006 16:15 GMT

The Format() function returns a String. You are perform a string comparison
instead of a date comparison. Hence the dates criteria do not work as you
expect.

Suggestions:
1. Explicitly declare the parameters so Access understands the correct type.

2. If this is an Append query, JET expects the structure below (where you
fill in the fields of the target table).

3. As with the dates, you can get a time value from a field and still have
it recognised as a Date/Time value by using TimeValue().

Try something like this:

PARAMETERS [Forms]![frmReports]![txtBeginDate] DateTime,
[Forms]![frmReports]![txtEndDate] DateTime;
INSERT INTO tblSessionQuery ( F1, F2, F3, F4, F5)
SELECT tblOrder.SessionDate,
tblMenus.Item_Name,
tblOrder.Type,
TimeValue(tblorder!SessionDate) AS SessionTime,
Sum(tblMenus.Item_Quantity) AS MenuCount
FROM tblOrder INNER JOIN tblMenus
ON tblOrder.Order_ID = tblMenus.Order_ID
WHERE tblOrder.SessionDate Between
[Forms]![frmReports]![txtBeginDate]
And [Forms]![frmReports]![txtEndDate]
GROUP BY tblOrder.SessionDate, tblMenus.Item_Name,
tblOrder.Type, TimeValue(tblorder!SessionDate)
ORDER BY tblOrder.SessionDate, tblMenus.Item_Name;

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a query in Access 2003 that has the following criteria
>
[quoted text clipped - 29 lines]
>
> DW

Reply to this Message

DW - 16 Apr 2006 15:29 GMT

Hi Mr. Browne,

Thanks for your response. I have tried declaring the parameters
explicitly.
This did not change the query results. The query is a select query,
not an append.
IF format$ returns a string, why do the 2005 dates get returned? If
these fields are returned as strings, the string 4/1/2005 is not
between the strings 4/1/2006 and 4/30/2006.
Any other suggestions?

Thanks,
David

Reply to this Message

DW - 16 Apr 2006 15:40 GMT

Also, if I remove the format$ function and just use between [begdate]
and [endate] the correct dates are returned. No 2005 dates. However,
if I run this query using the same date for begin and end, no records
are returned.

Thanks
DW

Reply to this Message

Allen Browne - 17 Apr 2006 02:25 GMT

If no dates are returned, there is probably a time component in the
SessionDate field.

Try changing the WHERE clause so it asks for less than the next day, i.e.:

WHERE (tblOrder.SessionDate >= [Forms]![frmReports]![txtBeginDate])
And (tblOrder.SessionDate < ([Forms]![frmReports]![txtEndDate]+1))

You asked why the 2005 values are included when making a string comparision.
Strings work alphabetically, character by character. For example,
Between "goo" And "goof"
would include good and goodly, but not gopher (before) or goofy or golf
(after). The 2005 at the *end* of the string falls between the characters at
the beginning of the string. If you actually put the word 04/02/2005 into a
dictionary that is sorted alphabetically, it would come after 04/02/9999,
and before 04/03/1900.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Also, if I remove the format$ function and just use between [begdate]
> and [endate] the correct dates are returned. No 2005 dates. However,
[quoted text clipped - 3 lines]
> Thanks
> DW

Reply to this Message

DW - 17 Apr 2006 03:42 GMT

Mr Browne,

That last code did the job. This has been a very aggravating
experience.
However, I did learn alot about Access that I did not know.

Thanks again for your help,
DW

Reply to this Message