Data Type mismatch with autonumber

[Aranyx]

I've been working on this one for a while and it has me completely
stumped. I have a form with several text boxes in it and a submit
button. When the submit button is pressed, I create a command variable
and do an update statement to one of my tables. However, I keep
getting an error of "Data Type Mismatch in Criteria Expression". I
looked at my criteria (which is matching to an autonumber field) and
didn't see any single quotes. When I did a msg box and had it output
my SQL, it resulted in:

UPDATE tblAssets SET [LabStaffUse] = 'Disposed', [Status] =
'Disposed-Internal', [DateModified] =
DATE(),[PhysicalInventoryDate]=DATE(),[UserUpdated] = 'XXXX'
WHERE [ID] = 1

I don't have quotes around the 1, so I don't know why it would result
in a data type mismatch in criteria expression. If anyone has run into
something similar and could help me out, I would appreciate it.

Thanks

 

[Rob Oldfield]

I'd tend not to completely trust the error message and suspect a type
mismatch somewhere else. What happens if you break it down so that instead
of a single SQL statement you use five i.e. where the first is

UPDATE tblAssets SET [LabStaffUse] = 'Disposed' WHERE [ID] = 1

Do they all work? If so, do they work if you lose the criteria?

 

[Aranyx]

Thanks Rob. That seems to work fine. Only problem was that was that I
abridged the SQL statement because I update 12 fields and I didn't feel
like retyping everything in the messagebox. I suppose that was pretty
dumb of me though, because it would appear the error is somewhere in
there.

Another thing I tried creating a recordset and selecting using the same
where clause and that worked fine. It also didn't work when I tried a
criteria of another unique field from the table. So the error I would
agree with you Rob not to trust the error message.

I can try posting my VBScript, since the error will probably be in
there.

cmd2.CommandText = "UPDATE Assets " _
& "SET Assets.LabStaffUse = 'Disposed', " _
& "Assets.Status = 'Disposed-Internal', " _
& "Assets.DateModified = DATE(), " _
& "Assets.PhysicalInventoryDate = DATE(), " _
& "Assets.UserUpdated = '" & txtUser.Value & "'," _
& "Assets.EmployeeNumber = '" & cmbCampus.Value &
txtRoomNumber.Value & "', " _
& "Assets.Locked = 'No', " _
& "Assets.EligibleForRefresh = 'No', " _
& "Assets.LastName = '" & strLast & "'," _
& "Assets.FirstName = '" & strFirst & "'," _
& "Assets.CustodianName = '" & txtCustodian.Value & "', " _
& "Assets.InventoryStatus = 'Disposed', " _
& "Assets.RecordStatus = 'No' " _
& "WHERE Assets.ID = " + strID + ";"

Thanks for the help.

 

[Aranyx]

Well, I found the error. Record Status is a yes/no box, so I simply
changed it to False and instead of 'No' and it seems to work fine.

 

[Rob Oldfield]

Hmmm. Rather than use a msgbox to grab the text that you've built... try
adding a breakpoint (Debug, Toggle Breakpoint) on the next line after your
string building (provided that it's not just a dim statement or similar).
Then run the code by opening the form and doing whatever it is that triggers
it. The code will stop at that line.

Next open the immediate window (View, Immediate Window). In there, type
?cmd2.commandtext and hit enter. That will give you your built string.

Copy that and paste it into the SQL view of a new query (you might need to
lose the opening and closing speech marks). Start a new query, then View,
SQL View, delete whatever is already there and then paste your text. Then
try switching to design view. If that works, try running the query and see
if you get a more meaningful error.

What does that give you?

 

[Rob Oldfield]

Good. Following on from my other post, it's probably worth running through
anyway as it's a useful technique to understand. I was going to go on to
suggest deleting columns one at a time and then rerunning the slimmed down
query to see if you nail down which field was causing a problem.

 

[Aranyx]

Alright, thanks for the help Rob