Vba sql Date Time identifier

[johnnyc]

Hey Guys,

i am having an issue with sql in vba using a variable for a date time field.

i know you identify date variables with #
i know you identify text variables with '

But now i have a date time field that is set to date but when i run the sql it does not find a match. i have confirmed that both rec and ercd match.

Also, when i change the table to text, i am able to match with single quote but i would like to keep the table as date if i can.

Please help!!

Below is the sql statement in question

isql = "SELECT * FROM Table1 WHERE from = '" & nm & "' AND rec = #" & ercd & "# AND sub = '" & esub & "'"

 

[CJ_London]

from is a reserved word, change it to something else and see if you still have the problem.

I would also post some example data and criteria you are using

 

[johnnyc]

Hey CJ,

when i change the Table1 field "rec" to text format and use the below script it works, so i dont think it has to do with reserved word.
i think i am indicating a date time variable incorrectly by using "#". Do you know if there is another indicator for date time instead of "#"?

Below is the original script and then the modified script when i change the field type to text which works.... i am looking for another solution

Original script

isql = "SELECT * FROM Table1 WHERE from = '" & nm & "' AND rec = #" & ercd & "# AND sub = '" & esub & "'"

Script when field is changed to text that works, just looking for another alternative. i would like to keep the date time as a date time format.

isql = "SELECT * FROM Table1 WHERE from = '" & nm & "' AND rec = '" & ercd & "' AND sub = '" & esub & "'"

let me know your thoughts

 

[Cronk]

Firstly, format your date so that
...rec = #" & format(ercd, "mm/dd/yyyy") & "#

Secondly, be aware that if your recorded date/time stamp includes the time, your query will only return records on midnight of the selected day. So put
...Date(rec) = #" & format(ercd, "mm/dd/yyyy") & "#

 

[CJ_London]

i would like to keep the date time as a date time format

you do this in the query or form/report. A date/time is actually a decimal number, the value to the left of the dp is the date so today is 42067 and the time is to the right of the db is the time expressed the number of seconds as a percentage of the number of seconds in the day - so 12 noon is .5, 10:55 is .456678 etc

the format is a text equivalent which is displayed per your pc settings so in the uk 42067.456678 is 04/03/2015 10:55 but you can change this to the US format with format(now(), "mm/dd/yyyy hh:mm")

 

[Minty]

i have confirmed that both rec and ercd match.

Check this thread for some interesting date comparison problems http://www.access-programmers.co.uk/forums/showthread.php?t=274554