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