We help IT Professionals succeed at work.
Get Started

msaccess sql date change from sql to design view

83 Views
Last Modified: 2017-11-02
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
Comment
Watch Question
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
Unlock 3 Answers and 21 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE