SQL Date filter problem

[iheartvba]

Hi I have a query in access as follows:

strSqlBANK = "SELECT Dt , SumOfAmount " & _
"FROM tblCASHBanked WHERE Dt = #" & dtBank & "#"
 

dtBank has been dimmed as a date

What happens is that dtBank will = 01/07/2008, but when I interogate the recordset !Dt = 07/01/2008.

I get two different values
when I run the following code:

msgbox Format(dtBank,"dd mmmm yyyy")

the result is : 01 July 2008
however when I run this code:

rst.Open strSqlBANK, cnn, adOpenDynamic, adLockOptimistic
with rst
msgbox Format(!dt,"dd mmmm yyyy")
end with
 

The result is: 07 January 2008

It is driving me absolutley nuts

[FishVal]

SQL accepts date constants in m/d/y format only.

Regards,
Fish

[iheartvba]

ok, Thanks a lot FishVal