MultiSelect Listbox as criteria Numeric vs. Text

[greenfch]

I am attempting to get the values from a multiselect listbox (access 2003) where the listbox contains 2 columns. the viewable text (column 2) and the associated number (column 1) to use as criteria in a report. the code I am using is close but produces exclamations around my number as if it were text. I can't seem to make it recognize as numeric.

Dim varItem As Variant
For Each varItem In Me.lstCauses.ItemsSelected
stLinkCriteria = stLinkCriteria & "[LLQID] =" _
& Chr(39) & Me.lstCauses.Column(0, varItem) & Chr(39) & " Or "
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 4) 'Remove the last " Or "

stlinkcriteria = [LLQID] = '1'

Produces error: "Datatype mismatch in criteria expression"
How do I get stlinkcriteria to show: [LLQID] = 1 (no exclamations)?

 

[pr2-eugin]

Hello greenfch, Welcome to aWF..

Well you ask the compiler to add single quotes and treat it as a String in the code you have..

Dim varItem As Variant

For Each varItem In Me.lstCauses.ItemsSelected
    stLinkCriteria = stLinkCriteria & "[LLQID] =" & [COLOR=Red][B]Chr(39)[/B][/COLOR] & Me.lstCauses.Column(0, varItem) & [COLOR=Red][B]Chr(39)[/B][/COLOR] & " Or "
Next varItem

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 4) 'Remove the last " Or "

Should it simply not be..

stLinkCriteria = stLinkCriteria & "[LLQID] = " & Me.lstCauses.Column(0, varItem) & " Or "

Also what is that you are doing in this line??

stlinkcriteria = [LLQID] = '1'

 

[greenfch]

Thanks for you rapid reply, eugin.
This is a multiselect listbox, that is why the variant.

I should have said:
stlinkcriteria is returned as [LLQID] = '1' instead of [LLQID] = 1 as what works in my criteria.
It was not meant to appear as code.

 

[pr2-eugin]

Okay, try the following..

Dim varItem As Variant

For Each varItem In Me.lstCauses.ItemsSelected
    stLinkCriteria = stLinkCriteria & "[LLQID] = " & Me.lstCauses.Column(0, varItem) & " Or "
Next varItem

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 4) 'Remove the last " Or "

 

[greenfch]

I feel so stupid.
It is my Monday.
Thanks again.
Cheers back atchya,