Avatar of sydneyguy
sydneyguy
Flag for Australia asked on

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
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
sydneyguy

8/22/2022 - Mon
Pawan Kumar

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)
Pawan Kumar

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

sydneyguy

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Norie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sydneyguy

ASKER
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
sydneyguy

ASKER
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
ASKER CERTIFIED SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sydneyguy

ASKER
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 ?????
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sydneyguy

ASKER
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
Ryan Chong

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
Norie

Did you try my suggesting of using the ISO date format yyyy-mm-dd?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sydneyguy

ASKER
i can use 43041 which is the number for 2/11/2017 and this works ok may be this is the way to go
Pawan Kumar

Thats excellent you can do that also for comparison.
sydneyguy

ASKER
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"));
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Norie

How exactly is DateVal declared and its value set?
Pawan Kumar

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.
sydneyguy

ASKER
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)"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Pawan Kumar

cool :)
Ryan Chong

>> WHERE (((greyhound.DATE1)=" & dat & ")

what's the data type of Date1 here? for date values in Access, you should use # ... #
sydneyguy

ASKER
looks like this will get the problem rounded not caring what format is used for me looks good
again thanks for your help
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.