Date problems in SQL recordset statements

[Silvester]

Using a query I access data in my table based on a date and time criteria
entered on frmMyTrans. It workd fine on development machine but when
distributed it does not work on all machines.

Date format - "mm/dd/yyyy"
Time format - "hh:mm AMPM"

The SQL statement below works fine on some computers but does not find the
data on others, depending on the date/time format of the user machine.
rst.recordcount should return 1 as one matching record definitely exists in
the table.

The error may be that qryMyTransactions.MeetingDate appears as #04/03/04# on
some machines. The query is searching for date in mm/dd/yyyy format, ie:
#03/04/2004#. Therefore the query does not find the data.

Can someone please help me with a solution ?


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as string
Set db = CurrentDb

strSQL = "SELECT *"
strSQL = strSQL & " FROM qryMyTransactions"
strSQL = strSQL & " WHERE (((qryMyTransactions.MeetingDate)=#" &
Forms!frmMyTrans![txtShortDate] & "#)"
strSQL = strSQL & " AND ((qryMyTransactions.MeetingTiming)=#" &
Forms!frmMyTrans![txtFormatTime] & "#))"
strSQL = strSQL & " ORDER by qryMyTransactions.Order"
strSQL = strSQL & " WITH OWNERACCESS OPTION;"

Set rst = db.OpenRecordset(strSQL)
MsgBox strSQL
MsgBox rst.RecordCount

 

[Allen Browne]

Explicitly format the date mm/dd/yyyy, i.e.:
"WHERE MeetingDate = " & Format(Forms!frmMyTrans![txtShortDate],
"\#mm\/dd\/yyyy\#")

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

 

[Silvester]

Thank God for people like you Allen.

Changing what you suggested and tweaking the sql slightly by adding a ")"
got it working perfectly. Thanks again !

strSQL = "SELECT *"
strSQL = strSQL & " FROM qryMyTransactions"
strSQL = strSQL & " WHERE (((qryMyTransactions.MeetingDate)=#" &
Format(Forms!frmMyTrans![txtShortDate],
"\#mm\/dd\/yyyy\#")
strSQL = strSQL & ") AND ((qryMyTransactions.MeetingTiming)=#" &
Forms!frmMyTrans![txtFormatTime] & "#))"
strSQL = strSQL & " ORDER by qryMyTransactions.Order"
strSQL = strSQL & " WITH OWNERACCESS OPTION;"

Explicitly format the date mm/dd/yyyy, i.e.:
"WHERE MeetingDate = " & Format(Forms!frmMyTrans![txtShortDate],
"\#mm\/dd\/yyyy\#")

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using a query I access data in my table based on a date and time criteria
entered on frmMyTrans. It workd fine on development machine but when
distributed it does not work on all machines.

Date format - "mm/dd/yyyy"
Time format - "hh:mm AMPM"

The SQL statement below works fine on some computers but does not find the
data on others, depending on the date/time format of the user machine.
rst.recordcount should return 1 as one matching record definitely exists in
the table.

The error may be that qryMyTransactions.MeetingDate appears as

#04/03/04#

on
some machines. The query is searching for date in mm/dd/yyyy format, ie:
#03/04/2004#. Therefore the query does not find the data.

Can someone please help me with a solution ?


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as string
Set db = CurrentDb

strSQL = "SELECT *"
strSQL = strSQL & " FROM qryMyTransactions"
strSQL = strSQL & " WHERE (((qryMyTransactions.MeetingDate)=#" &
Forms!frmMyTrans![txtShortDate] & "#)"
strSQL = strSQL & " AND ((qryMyTransactions.MeetingTiming)=#" &
Forms!frmMyTrans![txtFormatTime] & "#))"
strSQL = strSQL & " ORDER by qryMyTransactions.Order"
strSQL = strSQL & " WITH OWNERACCESS OPTION;"

Set rst = db.OpenRecordset(strSQL)
MsgBox strSQL
MsgBox rst.RecordCount