dbDate - type mismatch error

I'm having a problem with the following code:

***************************************************
Private Function UpdateTableStatus(status As String) As Boolean

On Error GoTo updateError

Dim query As String
Dim dbRecordset As Recordset

query = "SELECT * " & _
"FROM processDate WHERE " & _
"PROCDATE = '" & todayDate & "'"

Set dbRecordset = db.OpenRecordset(query, dbOpenDynaset)

'see if it was found
If dbRecordset.BOF = True And dbRecordset.EOF = True Then
'not found so add it
With dbRecordset
.AddNew
.fields("PROCDATE").Value = todayDate
.fields("STATUS").Value = status
.Update
End With
Else
'record was found, so update it
With dbRecordset
.Edit
.fields("STATUS").Value = status
.Update
End With
End If

dbRecordset.Close
Set dbRecordset = Nothing

UpdateTableStatus = True
Exit Function

updateError:
MsgBox "Error updating table. Error Number: " & CStr(Errors(0).Number) & _
" Description: " & Errors(0).Description & _
" source: " & Errors(0).Source, _
vbOKOnly + vbCritical, "Update Table Status"
UpdateTableStatus = False

End Function
****************************************************

In the query, todayDate is a Date Type defined elsewhere.
I don't get an error when I'm adding a row (first if). But when I call the function a second time to edit (a row exist at this point), I get a 3464 message: Data type mismatch in criteria expression.

Why would it work when there is nothing on the table, but as soon as there is data, the query doesn't work? Do I have to do some kind of conversion?


Thanks for your help.

Try Formatting your date string using the Format procedure!

Make sure it's the same as the databases date which would be mm,dd,yy more than likely!!

later
b :)

Thanks for your reply....

I found the problem with my code. On the query I surround the date with quotes. It's fine when it does an update and the table is empty. But it gets a mismatch on the second pass because it found an entry and it tries to compare a date to a string.

I took out the quotes and I don't get a mismatch but I also don't get a match. I looked further in my books and found that dates need to be surrounded by #. So when querying a date field, the date should look like this: #04/17/2002#.

I did this and it works.

I hope this helps someone in the future. :D