The 'Where' Clause & List Box 1

[pdldavis]

Hi, I am having trouble picking up items past the first item selected from a list box and was wondering if someone would have a look at this 'Where' statement and tell me what I am doing wrong.

If (lstTagType.ItemsSelected.Count = 0) Then Exit Sub
For Each varItem In lstTagType.ItemsSelected
StrsqlWhere = "WHERE ELECTblModuleInfo.ModTag =[Forms]![ELECfrmModRpts]![lstBulkMod] AND [ELECtblItemType].[itemType] = '" & (lstTagType.Column(0, varItem)) & "' OR "
Next varItem

Thanks, Dan

 

[GingerR]

You are resetting your StrSQLWhere each time. Build the list box WHERE clause separately then concantonate it to the main SQL Where clause when you're done:

dim strWhereListBox as String
strWhereListBox = ""

    For Each varItem In lstTagType.ItemsSelected
    if len(strWhereListBox) = 0 then
        strWhereListBox = "[ELECtblItemType].[itemType] = '" & (lstTagType.Column(0, varItem)) & "'"
    Else
        strWhereListBox = strWhereListBox & " OR [ELECtblItemType].[itemType] = '" & (lstTagType.Column(0, varItem)) & "'"
     End if
Next varItem

if len(strWhereListBox) > 0 then strWhereListBox = " And (" & strWhereListBox & ")"

StrsqlWhere = "WHERE ELECTblModuleInfo.ModTag =[Forms]![ELECfrmModRpts]![lstBulkMod]" & strWhereListBox

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[pdldavis]

Hi, thanks for the response and the help. It almost works (sigh of relief).

I included
dim strWhereListBox as String
strWhereListBox = ""

and the subsequent changes and it looks like the string is missing a couple 'AND's. I debug printed the string and got the following:

WHERE ELECTblModuleInfo.ModTag =[Forms]![ELECfrmModRpts]![lstBulkMod][ELECtblItemType].[itemType] = 'J-Box' OR [ELECtblItemType].[itemType] = 'Outdoor Light'

It runs the two criteria together so I manually inserted an 'AND'.

WHERE (((ELECTblModuleInfo.ModTag)=[Forms]!ELECfrmModRpts]![lstBulkMod]) AND((ELECtblItemType.itemType)='J-Box' Or (ELECtblItemType.itemType)='Outdoor Light'));

Where would I insert the 'And' in the string?

Thanks, Dan

 

[GingerR]

did you include this bit from the previous post?

if len(strWhereListBox) > 0 then strWhereListBox = " And (" & strWhereListBox & ")"

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[pdldavis]

Whoops! I forgot that bit. It works fine now. Thank you for your help.

Dan