Data type mismatch in criteria expression

[Tep]

Hi,

I have a data type mismatch in criteria expression with the following code:

Dim SQL As String
Dim db As DAO.Database
Dim varItem As Variant
Set db = CurrentDb()
Dim i As Integer
 For i = 0 To Me.ListSelectedNatcd.ListCount - 1
 Me.ListSelectedNatcd.Selected(i) = True
 Next i
For Each varItem In Me.ListSelectedNatcd.ItemsSelected
SQL = "UPDATE Mapping " & _
          "SET Mapping.FoodEx1_cd = '" & Me.FE1_cd.Value & "' " & _
          "WHERE Mapping.Nat_cd = " & varItem
DoCmd.RunSQL SQL
Next varItem

All the fields have Text data type (FoodEx1_cd and Nat_cd in table mapping and source fields of ListSelectedNatcd (Listbox) and FE1_cd (unbound text box).

I thought that I made a mistake in the Where-clause and tried this:

"WHERE Mapping.Nat_cd = '" & varItem '

But this gave the error Syntax error in string query expression 'Mapping.Nat_cd='0'.

And I tried:

"WHERE Mapping.Nat_cd = " & CStr(varItem)

But this resulted again in a data type mismatch.


I hope someone can help me!
Thank you in advance,
Tep

 

[DJkarl]

Close with this

"WHERE Mapping.Nat_cd = '" & varItem '

"WHERE Mapping.Nat_cd = '" & varItem  & "'"

 

[Tep]

Thank you very much! It does work now.
However, another problem occurs :-(
When I run the code it says "You are about to update 0 rows", while I am sure there are rows that should be updated. Do you have any ideas what the problem can be?
Thanks again,
Tep

 

[Tep]

I think it does not recognize the varItem in the Where clause, because it does work fine when I test the code with:
<CODE>"WHERE Mapping.Nat_cd = '9600'"</CODE>
And I also tested if varItems are present.
How should I refer to the varItem?
Thanks again

 

[pr2-eugin]

Tep, Why is that you have declared the varItem as Variant? In that case you have to convert it to String using CStr..

"WHERE Mapping.Nat_cd = '" & CStr(varItem)  & "'"

Also use CurrentDb.Execute rather than using DoCmd.RunSQL.. This will avoid the confirmation message popping up saying you are about to make changes.. Also is a bit faster..

 

[Tep]

Hi pr2-eugin, Thank you for looking at the problem. I understood that Variant could cope with all data types. When I change the Where-clause as you suggested it still does not update any rows (with message that it is updating 0 rows).

 

[pr2-eugin]

Okay before performing the Update, just print out the SQL query and see what you are getting..

For Each varItem In Me.ListSelectedNatcd.ItemsSelected
SQL = "UPDATE Mapping " & _
          "SET Mapping.FoodEx1_cd = '" & Me.FE1_cd.Value & "' " & _
          "WHERE Mapping.Nat_cd = " & varItem
Debug.Print SQL
          [COLOR=Green]'DoCmd.RunSQL SQL[/COLOR]
Next varItem

See what you get in the immediate window.. also see if you are getting any result using the normal Query window..

 

[Tep]

This is the result in the immediate window:

UPDATE Mapping SET Mapping.FoodEx1_cd = 'A.01.000001' WHERE Mapping.Nat_cd = 0

The FoodEx1_cd is correct, but the Nat_cd not. So it does not recognize the varItem, I think.
The varItem is however recognized in a similar code:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Set db = CurrentDb()
Dim i As Integer
Set rs = db.OpenRecordset("Mapping", dbOpenDynaset, dbAppendOnly)
 For i = 0 To Me.ListSelectedNatcd.ListCount - 1
 Me.ListSelectedNatcd.Selected(i) = True
 Next i
For Each varItem In Me.ListSelectedNatcd.ItemsSelected
rs.AddNew
rs!Nat_cd = Me.ListSelectedNatcd.Column(0, varItem)
rs!FoodEx1_cd = Me.FE1_cd.Value
rs.Update
Next varItem
[Mapped].Requery
 For i = 0 To Me.ListSelectedNatcd.ListCount - 1
 Me.ListSelectedNatcd.Selected(i) = False
 Next i

I do not understand your suggestion for putting the code in normal Query window.

I hope that we can solve this...
Thanks,
Tep

 

[pr2-eugin]

Okay.. in simple English, could you just walk me through what is that you want to do? I do not understand what you are trying to do here..

For i = 0 To Me.ListSelectedNatcd.ListCount - 1
    Me.ListSelectedNatcd.Selected(i) = True
Next i

 

[Tep]

There I select all the records in the Listbox ListSelectedNatcd, so that ItemsSelected uses all records in the loop.
At the end of the code I do a deselect of all the records:

For i = 0 To Me.ListSelectedNatcd.ListCount - 1
Me.ListSelectedNatcd.Selected(i) = False
Next i

I have found this code somewhere on the web

 

[DJkarl]

Since you are selecting every item in the list, why bother using the selected property at all, put your code in a modified version of the first loop.

 For i = 0 To Me.ListSelectedNatcd.ListCount - 1
    SQL = "UPDATE Mapping " & _
          "SET Mapping.FoodEx1_cd = '" & Me.FE1_cd.Value & "' " & _
          "WHERE Mapping.Nat_cd = '" & Me.ListSelectedNatcd.Columns(0,i) & "'"
    DoCmd.RunSQL SQL
 Next i

 

[Tep]

That's it! Thank you all very much!
Without your suggestions I would never have solved this
Great!