Case function

[Jessica]

Hello,

I have this as a case function in one of my forms but it returns a
run-time error 13 Type mismatch. Can anyone tell me what am I doing wrong?

Thank you,
Jess


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 "06010 11292" To "06010 11588", "76808 52094", "76808 52137"
DoCmd.OpenReport "report 1", acViewPreview, , "UPC = '" &
lngUPC & "'"

 

[Mark]

If you set a variable to a Long datatype, it expects an integer number. You
don't say which line is causing the problem, but it's probably happening
when you try to set lngUPC=UPC.ItemData(varSelectedUPC) if varSelectedUPC is
two numbers separated by a space.

If the UPC code in varSelectedUPC is supposed to have a space in it, then
you should change lngUPC to a string variable instead of a long integer
variable.

"Jessica" wrote ...

 

[James Hahn]

The variable you are using for the case statement (lngUPC) is declared as
numeric (long), but you are comparing it to a string. In any case, a long
could never be 06010 11292 because numbers can't have a space in the middle.
You need to decide whether it's numeric or string, and be consistent
throughout.

 

[Jessica]

Thanks Mark and James

I switched it to a string variable but, when I select more than one
report it only shows the first report that I selected. Is there
something else I need to change?

Private Sub command15_Click()
Dim varSelectedUPC As Variant
Dim StrUPC As String

For Each varSelectedUPC In UPC.ItemsSelected
StrUPC = UPC.ItemData(varSelectedUPC)
Select Case StrUPC
Case "06010 11292" To "06010 11297", "76808 52094", "76808 52138"
DoCmd.OpenReport "report 1", acViewPreview, , "UPC = '" &
StrUPC & "'"

Thanks,
Jessica

 

[Mark]

Since you're opening the report as soon as strUPC matches your case, it's
only one value, so when you pass "UPC = '" & StrUPC & "'" to the where
clause of the OpenReport function, it will only show that one record.

What you'll need to do is build an SQL statement inside your For...Next
loop, and then open the report once that loop is complete. Try this; you
might need to tweak it a little:

Private Sub command15_Click()
Dim varSelectedUPC As Variant
Dim StrUPC As String, strSQL as String

For Each varSelectedUPC In UPC.ItemsSelected
StrUPC = UPC.ItemData(varSelectedUPC)
Select Case StrUPC
Case "06010 11292" To "06010 11297", "76808 52094", "76808 52138"
strSQL = strSQL & "UPC = '" & strUPC & "' OR "
End Select
Next varSelectedUPC
strSQL = Left(strSQL, Len(strSQL) - 4)
DoCmd.OpenReport "report 1", acViewPreview, , strSQL
End Sub


...