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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.