Checking for a date in a table by using a recordset

  • Thread starter Thread starter Carlos
  • Start date Start date
C

Carlos

I have records that contain the date they were recorded. The table I
use has a field called DateEntered formatted in date/time (mm-dd-yyyy).
I use a SQL statement when opening the recordset:

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim recordsetZZ As New ADODB.Recordset
recordsetZZ.ActiveConnection = cnn1
Dim mySQL as String

mySQL = "SELECT Table.ID, Table.DateEntered FROM Table"
mySQL = mySQL + " WHERE [ID] = " & """" & CertainID & """"
mySQL = mySQL + " AND [DateEntered] = " & "'" & date & "'"

recorsetZZ.Open mySQL, , adOpenKeyset, adLockPessimistic

If recorset.EOF Then
Msgbox "Record Not Found"

Else

Msgbox "Record Found"

End If

recordsetZZ.close


I keep getting this message:
DateType Mismatch in criteria expression. I've verified the data type
in the table, making sure that it looks like date while in a
breakpoint, but I can't seem to figure this one out.

Any hints?

Carlos
 
Carlos,

Try:
mySQL = "SELECT Table.ID, Table.DateEntered FROM Table"
mySQL = mySQL + " WHERE [ID] = " & """" & CertainID & """"
mySQL = mySQL + " AND [DateEntered] = #" & date & "#"
Click to expand...

HTH
Dale

Carlos said:
I have records that contain the date they were recorded. The table I
use has a field called DateEntered formatted in date/time (mm-dd-yyyy).
I use a SQL statement when opening the recordset:

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim recordsetZZ As New ADODB.Recordset
recordsetZZ.ActiveConnection = cnn1
Dim mySQL as String

mySQL = "SELECT Table.ID, Table.DateEntered FROM Table"
mySQL = mySQL + " WHERE [ID] = " & """" & CertainID & """"
mySQL = mySQL + " AND [DateEntered] = " & "'" & date & "'"

recorsetZZ.Open mySQL, , adOpenKeyset, adLockPessimistic

If recorset.EOF Then
Msgbox "Record Not Found"

Else

Msgbox "Record Found"

End If

recordsetZZ.close


I keep getting this message:
DateType Mismatch in criteria expression. I've verified the data type
in the table, making sure that it looks like date while in a
breakpoint, but I can't seem to figure this one out.

Any hints?

Carlos
Click to expand...
 
Thank you, Dale. Your solution worked. I would not have thought to
use the # sign.

Carlos
 
Glad to help

In Access, date values have to be set off by the # symbol. In SQL and
Oracle, they use the apostrophe.

Dale
 
Back
Top