PLease HElp - Date and TIme syntax in Query

[Alan]

Sorry but I dont understand what I am Missing here and have been searching
for the last 2 days


I have a simple query to create a record set.

SELECT tbl_Daily.* FROM tbl_Daily WHERE (((tbl_Daily.DateTime)=#11/8/2008
2:13:0#))

This syntax works fine and returns me 2 rows as expected

however when I try to use this from within a Form and substitute the date
for a value displayed within the form I get 0 rows returned ???


Dim dtDateTime As Date
Dim rstUpdateDailyData As Recordset

dtDateTime = Format(Me.DateTime, "dd/mm/yyyy hh:mm:ss")

Set rstUpdateDailyData = CurrentDb.OpenRecordset("SELECT tbl_Daily.* FROM
tbl_Daily WHERE (((tbl_Daily.DateTime)=#" & dtDateTime & "#))", dbOpenDynaset)

There must be a simple answer for the dim, but I cannot see it

Any Help would be most graefully appreciated

 

[Allen Browne]

Regardless of your local regional settings, JET expects a literal date in a
string to be in American format.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Copy the SQLDate() function, and use:
Dim strSql As String
strSql = "SELECT tbl_Daily.* FROM tbl_Daily WHERE tbl_Daily.[DateTime] =
" & SQLDate(Me.DateTime) & ";"
Set rs = CurrentDb.OpenRecordset(strSql)

If you have difficutly, add the line:
debug.Print strSql
When it fails open the Immediate Window (Ctrl+G) and look at the output.

Note that DATETIME is a reserved word in JET SQL, so not a good name for a
field. Adding square brackets can help, but you might like to use this list
when designing table, and avoid these names:
http://allenbrowne.com/AppIssueBadWord.html

 

[Alan]

Allen

Many Thanks for your help and prompt response

Regards

Alan

Regardless of your local regional settings, JET expects a literal date in a
string to be in American format.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Copy the SQLDate() function, and use:
Dim strSql As String
strSql = "SELECT tbl_Daily.* FROM tbl_Daily WHERE tbl_Daily.[DateTime] =
" & SQLDate(Me.DateTime) & ";"
Set rs = CurrentDb.OpenRecordset(strSql)

If you have difficutly, add the line:
debug.Print strSql
When it fails open the Immediate Window (Ctrl+G) and look at the output.

Note that DATETIME is a reserved word in JET SQL, so not a good name for a
field. Adding square brackets can help, but you might like to use this list
when designing table, and avoid these names:
http://allenbrowne.com/AppIssueBadWord.html