i have some code that is assembled in code see below, the code is assembled see below for the result
strSQL = "SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound " & _
" WHERE (((greyhound.DATE1)=#" & DateVal & "#) AND ((greyhound.RaceNo)='" & rsDao![RaceNo] & "') AND ((greyhound.Track)='" & rsDao![Track] & "')) And ((Distance) Is Null)"
' MsgBox strSQL
Set recExists = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Do While recExists.EOF = False
recExists.Edit
recExists![Distance] = rsDao![Distance]
recExists.Update
recExists.MoveNext
Loop
rsDao.MoveNext
**************************
**********
*****
result of assembled sql result
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound WHERE (((greyhound.DATE1)=#2/11/
2017#) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS'))
And ((Distance) Is Null)
the date that is returned is the #2/11/2017#
there are records there but does not find them but they do exist.
![photo of query with date translation]()
if the sql is copied form the code and then placed in the sql build area then switched to the design view the date gets transposed from 2/11/2017 to 11/2/2017
my question is why is this happening
i have run it using 31/10/2017 and it works fine as there is no 31 month doe not change
but the 2/11/2017 does
any ideas on what is happening why and what i can do about it
thanks for you help in advnce
DateValue function around date.
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound
WHERE ((DATEVALUE(greyhound.DATE1)=#2/11/20
AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS'))