Re: Criteria Compare dates using between and dates from other years are included
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sat, 15 Apr 2006 23:15:19 +0800
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;
--
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.
"DW" <dawatson833@xxxxxxx> wrote in message
news:1145076632.426816.21620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Prev by Date: Re: Fields not required
- Next by Date: Re: Error on QueryDef on Close
- Previous by thread: Criteria Compare dates using between and dates from other years are included
- Next by thread: Re: Criteria Compare dates using between and dates from other years are included
- Index(es):
Relevant Pages
|
