Date format issues with VBA Update SQL

[marabak]

Hey.

I have a form which contains 5 textboxes which are pre-populated with some information from a table. Fundamentally, I want the user to be able to make some changes and save it to the table (without being bound in case they change there mind etc.).

I have some VBA script which runs when the user presses Save, however, I am struggling with date formats etc. I've tried many combinations of Format()s and # but to no avail. On the form, I have four textboxes which are populated with data indirectly from the table along with a "hidden" textbox with the primary ID (pID) to identify the record that needs updating.

At the moment, I keep getting a data type mismatch in criteria expression when I run the code. The textboxes are formatted as Short Date with _ placeholders, the table has a short date format as well, so I am a little clueless.

Has anyone got any ideas on how I can format this and get it to work?

Thank you

Edit: I should probably add, the data in the textboxes is retrieved from a listbox which is populated from another VBA query

My code currently sits as

Private Sub EditSave_Click()
Dim updateSQL As String

'check primary key is working
If Len(pID & vbNullString) = 0 Then
MsgBox "There was a problem with the primary key."
Exit Sub
End If

'check data isn't blank
If Len(editserial & vbNullString) = 0 _
Or Len(editactivity & vbNullString) = 0 _
Or Len(editRefDate & vbNullString) = 0 _
Or Len(EditExpiry & vbNullString) = 0 Then
MsgBox "Please enter all details"
Exit Sub
End If

'run sql
updateSQL = "UPDATE OnSite SET SerialNo = " & editserial & ", Activity = " & editactivity _
& ", RefDate = #Format(" & Me!editRefDate & ", ""dd/mm/yyyy"")#, ExpDate = #Format(" & Me!EditExpiry & ", ""dd/mm/yyyy"")#" _
& " WHERE [ID] = '" & Me!pID & "';"
MsgBox updateSQL

DoCmd.RunSQL (updateSQL)

'message user
MsgBox "Capsule added successfully"

End Sub

 

[Minty]

Remove all your format constructs and simply put the dates in hashes e.g. #21/12/2104#

At the moment you are trying to format within the SQL query string which is unnecessary and won't work.

If you have a problem with UK US date formats format the dates out side the SQL string and again simply add the # # around them.

 

[marabak]

Hi Minty. Thank you for your reply. If I change the SQL to

'run sql
addSQL = "UPDATE OnSite SET SerialNo = " & editserial & ", Activity = " & editactivity _
& ", RefDate = #" & Me!editRefDate & "#, ExpDate = #" & Me!EditExpiry & "#" _
& " WHERE [ID] = '" & ID & "';"

then I get a runtime error 3464 - Data type mismatch in criteria expression

 

[marabak]

Ahh, I think the dates have been red herring!! After typing out the error message (rather than just reading it), I noticed it mentioned the criteria expression.

I had ' ' surrounding my ID so it was probably trying to compare a string to a table of integers. Removing these appears to have solved it (for now at least).

Thank you for your input.

 

[Minty]

Glad you found the problem. Numbers and strings in strings can catch anyone out!