Full Version: Data type mismatch troubleshooting.
UtterAccess Forums > Microsoft® Access > Access Forms
I have the following update code for saving data to a table:
im SQLcasualty_data As String
SQLcasualty_data = "UPDATE (casualtydata) " & _
"SET [cas_type] = '" & Form_subCasualtyData.CAS_Type.Column(1) & "'," & _
"[cas_manner] = '" & Form_subCasualtyData.CAS_Manner.Column(1) & "'," & _
"[Burial_Place] = '" & Form_subCasualtyData.txtBurialPlace.Column(1) & "'," & _
"[Burial_Date] = '" & Form_subCasualtyData.Burial_Date & "'," & _
"[Escort_Name] = '" & Form_subCasualtyData.Escort_Name & "'," & _
"[Escort_Command] = '" & Form_subCasualtyData.Escort_Cmd & "'," & _
"[Escort_Work] = '" & Form_subCasualtyData.Escort_Work & "'," & _
"[Escort_Home] = '" & Form_subCasualtyData.Escort_Home & "'," & _
"[Escort_Cell] = '" & Form_subCasualtyData.Escort_Cell & "'" & _
"WHERE [case_id_casualty] =" & Forms!NewCase!CaseID
CurrentDb.Execute SQLcasualty_data, dbFailOnError
The error resides in the boldfaced line. It is formated as a short date in both the form and the casualtydata table (data type of date/time. There is an input mask on the form to force it into the 99/99/0000 format. If I leave that field blank, I get the following error when I execute the SQL "Data type mismatch in criteria expression." If I enter a date, it works fine. If I change the field data type to text, and leave it blank, it works fine. But, if I change it to text, then when I sort, it does not sort by actual date, but by the number.
There are three or four instances of this in my DB, but if I can fix one, I can fix em all.
Usually, dates need to be enclosed in # providing the data type in the underlying table is Date/Time. you could try changing the highlighed line to
"[Burial_Date] = #" & Form_subCasualtyData.Burial_Date & "#," & _

see if that helps.
I tried that, and I get
yntax error in date in query expression '##'
I verified the underlying table is Date/Time
Still running into this error when I have a blank date.
When it is filled in, it works great. Blank = Datatype mismatch, and of course, you can not set Date/Time fields in a table to accept a zero length string.
If anyone has guidance on an Nz function usage, I would appreciate it.
Appologies for the late reply, not had internet access this weekend.
If the control Form_subCasualtyData.Burial_Date is empty, do you not want to run the update query, or do you still want the query to run, but not try and update the [Burial_Date] field?
You could try this:

IIF(Len(Nz(Form_subCasualtyData.Burial_Date,"")) = 0, "", "[Burial_Date] = '" & Form_subCasualtyData.Burial_Date & "',") & _

Replace you're highlighted line with the above line, that will check for 2 things, Zero Length Strings AND Null Values (zero length string is not the same as a null value). If the control on the form is blank, the date field won't get updated in the table, if the control on the form contains a date, then the date field should be updated.
I think I would go with something like:

"[Burial_Date] = " & IIf(IsDate(Form_subCasualtyData.Burial_Date), "#" & Form_subCasualtyData.Burial_Date & "#", "Null") & ", " & _
I will try both and update the post with the info.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.