dbDate - type mismatch error

[timcam01]

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.

[Beacon]

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

[timcam01]

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.