msaccess sql date change from sql to design view

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
sydneyguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Pls try this  

DateValue function around date.

SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  
WHERE ((DATEVALUE(greyhound.DATE1)=#2/11/2017#)
AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)
0
Pawan KumarDatabase ExpertCommented:
or we can like this also
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  
WHERE (((greyhound.DATE1)>=#2/11/2017#) AND (greyhound.DATE1)<#3/11/2017#)
AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)

Open in new window

0
sydneyguyAuthor Commented:
the first one throws up a data miss match the second is not the best way as the numbers need to be what is selected and doing criteria searches will not work on the form sheet.
any other ideas
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
NorieAnalyst Assistant Commented:
What happens if you format the dates to yyyy-mm-dd? 
strSQL = "SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound " & _
                " WHERE (((greyhound.DATE1)=#" & Format(DateVal, "yyyy-mm-dd") & "#) AND ((greyhound.RaceNo)='" & rsDao![RaceNo] & "') AND ((greyhound.Track)='" & rsDao![Track] & "')) And ((Distance) Is Null)"

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

always compare your date values in format such as mm/dd/yyyy or yyyy/mm/dd but not dd/mm/yyyy.

so what you need to do is to make sure the value of DateVal is presented in mm/dd/yyyy or yyyy/nn/dd format.
0
sydneyguyAuthor Commented:
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  WHERE (((greyhound.DATE1)=DATEVALUE(#2/11/2017#)) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)

this still transposes to 11/2/2017
0
sydneyguyAuthor Commented:
may be in america but in england and australi it is expected to be formatted dd/mm/yyyy
must be another answer i would not be the first person running into this problem
0
Pawan KumarDatabase ExpertCommented:
Is it giving you proper data.

SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  WHERE (((greyhound.DATE1)=DATEVALUE(#2/11/2017#)) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)

Open in new window


Date format db handles internally.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sydneyguyAuthor Commented:
if i put in a sql 2/11/2017 why would it feel the need to transpose it as it is placed in at 2nd of nov not 11th of feb
what diff does it make ?????
0
sydneyguyAuthor Commented:
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  WHERE (((greyhound.DATE1)=DATEVALUE(#2/11/2017#)) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)
no still transposes to 11/2/2017 very interesting
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if you want to search on 2 Nov 2017, you should try:

SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound  WHERE (((greyhound.DATE1)= #11/2/2017# ) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS')) And ((Distance) Is Null)
no still transposes to 11/2/2017 very interesting
0
NorieAnalyst Assistant Commented:
Did you try my suggesting of using the ISO date format yyyy-mm-dd?
0
sydneyguyAuthor Commented:
i can use 43041 which is the number for 2/11/2017 and this works ok may be this is the way to go
0
Pawan KumarDatabase ExpertCommented:
Thats excellent you can do that also for comparison.
0
sydneyguyAuthor Commented:
Did you try my suggesting of using the ISO date format yyyy-mm-dd?  is not really the answer that i think i require as the data that i extract is in the format
dd/mm/yyyy  and would prefer to come up with a better solution to manipulating the format as not sure of other problems that this may cause but bringing it back to its primitive ie 2/11/2017 to its number  43041 will ensure that no incorrectness can come into the equation
what do you think


SELECT greyhound.NAME, greyhound.DATE1, greyhound.Track, greyhound.id, greyhound.Distance, greyhound.RaceNo
FROM greyhound
WHERE (((greyhound.DATE1)=43041) AND ((greyhound.Track)="gos"));
0
NorieAnalyst Assistant Commented:
How exactly is DateVal declared and its value set?
0
Pawan KumarDatabase ExpertCommented: 
SELECT greyhound.NAME, greyhound.DATE1, greyhound.Track, greyhound.id, greyhound.Distance, greyhound.RaceNo
FROM greyhound
WHERE (((greyhound.DATE1)=43041) AND ((greyhound.Track)="gos"));

Open in new window


Looks good to me.
0
sydneyguyAuthor Commented:
this seems to work will check it out and lock it in later thanks for the help most appreciated

 Dim dat As Double
        dat = DateVal  ' *******  where dateval = 2/11/2017
        strSQL = "SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound " & _
                " WHERE (((greyhound.DATE1)=" & dat & ") AND ((greyhound.RaceNo)='" & rsDao![RaceNo] & "') AND ((greyhound.Track)='" & rsDao![Track] & "')) And ((Distance) Is Null)"
0
Pawan KumarDatabase ExpertCommented:
cool :)
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> WHERE (((greyhound.DATE1)=" & dat & ")

what's the data type of Date1 here? for date values in Access, you should use # ... #
0
sydneyguyAuthor Commented:
looks like this will get the problem rounded not caring what format is used for me looks good
again thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.