run-time error 3464

[Jessica]

Hello All,

I tried this code in my database but keep getting a run-time error 3464
Data type mismatch in criteria expression. When I go and debug the code
it looks as if it is being directed to the correct report because
depending on which upc I select the DoCmd.OpenReport "report1",
acViewPreview, ,"UPC = " & lngUPC is highlighted. Does anyone have any
ideas of what might be wrong?

Private Sub cmdOpenReports_Click()
Dim varSelectedUPC As Variant
Dim lngUPC as Long

For Each varSelectedUPC In lstUPC.ItemsSelected
lngUPC = lstUPC.ItemData(varSelectedUPC)
Select Case lngUPC
Case 1111 to 3333
DoCmd.OpenReport "report1", acViewPreview, ,"UPC = " & lngUPC
'preview report; change to acViewormal to print immediately
Case 4444 to 6666
DoCmd.OpenReport "report2", acViewPreview, ,"UPC = " & lngUPC
Case Else

DoCmd.OpenReport "report3", acViewPreview, ,"UPC = " & lngUPC
End Select
Next varSelectedUPC
End Sub


TIA,
Jess

 

[Brendan Reynolds]

What's the data type of the UPC field? If it is text, you'll need quotes
around it ...

DoCmd.OpenReport "report1", acViewPreview, ,"UPC = '" & lngUPC & "'"

That's a single quote followed by a double quote after the "=", and a single
quote between two double quotes at the end.

 

[Jessica]

Thanks Brendan that worked perfectly. I have another question for anyone
that can help. Originally I asked how to set this code up using dummy
upc but have tried using actual numbers. It is either not allowing upc
numbers to have a space between them which is how they are entered in
the database and it doesn't accept the first number being a zero. Also
could I add to the case line of the code the range 06010 11292 To 06010
11588 and 76808 52137 and 76808 52138?

Private Sub command15_Click()
Dim varSelectedUPC As Variant
Dim lngUPC As Long

For Each varSelectedUPC In UPC.ItemsSelected
lngUPC = UPC.ItemData(varSelectedUPC)
Select Case lngUPC
Case 1111 To 3333



Thank you,
Jess