Using Date is a Problem in SQL? How to solve

[Cap2010]

Below sql is used in Access basic.
Tdate is already formatted as dd/mm/yyyy in the table as well as sql.
Have changed the system date to dd/mm/yyyy
Thus the date is 01st December, 2002 to be used.
When it process it changes to 12/01/2002.


How to make it accept 01/12/2002 (dd/mm/yyyy) and not as 12/01/2002
This happens at every 1st day of the month.

Select TDate,
ITemID,
Sum([Item1]) AS Itm1,
Sum([Item2]) AS Itm2,
[Itm1]+[Itm2] AS ItemTotal,
FROM MaterialTrans
GROUP BY TDate, ITemId HAVING MaterialTrans.TDate=#01/12/2002# ORDER BY IteMId;


Please help.

Cap

 

[mwolf00]

Using the datePart function....

HAVING datePart("m",MaterialTrans.TDate) = 12
AND datePart("d",MaterialTrans.TDate) = 1
AND datePart("yyyy",MaterialTrans.TDate) = 2002


The dateSerial function. This MAY NOT work....
HAVING MaterialTrans.TDate = dateSerial(2002,12,1)



---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com