Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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/
the date that is returned is the #2/11/2017#
there are records there but does not find them but they do exist.
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
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/
the date that is returned is the #2/11/2017#
there are records there but does not find them but they do exist.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pls try this
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'))
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'))
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)
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
any other ideas
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)"
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.
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound WHERE (((greyhound.DATE1)=DATEVA
this still transposes to 11/2/2017
this still transposes to 11/2/2017
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
must be another answer i would not be the first person running into this problem
Is it giving you proper data.
Date format db handles internally.
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)
Date format db handles internally.
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 ?????
what diff does it make ?????
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound WHERE (((greyhound.DATE1)=DATEVA
no still transposes to 11/2/2017 very interesting
no still transposes to 11/2/2017 very interesting
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'))
no still transposes to 11/2/2017 very interesting
SELECT DATE1, RaceNo, Gate, Track, Distance FROM greyhound WHERE (((greyhound.DATE1)= #11/2/2017# ) AND ((greyhound.RaceNo)='9') AND ((greyhound.Track)='GOS'))
no still transposes to 11/2/2017 very interesting
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)
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)
SELECT greyhound.NAME, greyhound.DATE1, greyhound.Track, greyhound.id, greyhound.Distance, greyhound.RaceNo
FROM greyhound
WHERE (((greyhound.DATE1)=43041) AND ((greyhound.Track)="gos"));
Looks good to me.
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)"
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)"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial