Runtime Error 3464 on Form Command Button

[danielle.EWAIG]

Hi,

I am currently creating a form which includes a command button to update a field in a table to show Today's Date, at the time of running a query I am still yet to write.

I have the following code for my Command Button, which is returning the Runtime Error 3464 (Data type mismatch in Criteria Expression):

Private Sub cmdAddTodaysDate_Click()
CurrentDb.Execute (" UPDATE tblMyTable SET TodaysDate = " & Date & " WHERE tblMyTable.ProductCode = " & Me.txtProductCode)

End Sub

The query above seems to be having issues matching the text from the 'txtProductCode' textbox to the table field 'ProductCode'.

I've searched forums/Google for where I've gone wrong with my code, but can't seem to find it. I'm a VBA newbie; have had much more experience in Excel.

Any help is greatly appreciated!


Danielle.

 

[GinaWhipp]

What is the Data Type of TodaysDate and the underlining Field txtProductCode?

 

[danielle.EWAIG]

Hi Gina,

TodaysDate is Date/Time data type. And txtProductCode is a textbox field, which is populated by a combo box.

Thanks.

 

[GinaWhipp]

Let's try...

CurrentDb.Execute (" UPDATE tblMyTable SET TodaysDate = " & Date & " WHERE tblMyTable.ProductCode = '" & Me.txtProductCode & "'")

 

[danielle.EWAIG]

Hi Gina,

Thanks! That's really close. It populates into the TodaysDate field, against the correct records (WHERE tblMyTable.ProductCode = Me.txtProductCode).

However, it's populating 30/12/1899 as the date. I've tried playing with the 'Date' part of the code to get it to return today's date, to no avail. I'm guessing it requires just a small tweak.

Should I open a new thread for this query (seeing as it doesn't relate to the original runtime error)? Or are you ok to answer it here?

 

[MarkK]

Whereas a string field value need to be delimited using quotes, a date field value needs to be delimited using the octothorpe, or "#" character, so try this as your SQL . . .

"UPDATE tblMyTable " & _
"SET TodaysDate = #" & Date & "# " & _
"WHERE ProductCode = '" & Me.txtProductCode & "'"

 

[danielle.EWAIG]

Thanks MarkK! It works perfectly
And I understand what the code's requirements were, based on your explanation. So thank you.

 

[TJPoorman]

If you wanted to also capture the time the query was run you could do:

"UPDATE tblMyTable " & _
"SET TodaysDate = Now()" & _
"WHERE ProductCode = '" & Me.txtProductCode & "'"