question about strFilter and AND/OR

[Jsak]

I’m currently working on an access07 database that will allow the users to select from 4 different list boxes which will allow them to filter records based on what they select and then display the results in a report.
I have 4 list boxes with the following names:
Lstpayment
lststore
lstkeyword
lstbuyer
This is the part I am stuck on:
When I use the code
strFilter = "[store_name] = '" & Me!lststore & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
it works perfectly for one list box (lststore) and generates the reports correctly. What I am having a problem with is joining additional listboxes to that command so it will filter based on all 4. I’ve tried the following for 2 listboxes and I get a type mismatch error:
strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '" & Me!lstpayment & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
I’ve tried various methods of the above and can’t get them to work. Can anyone tell me what I am doing wrong?

 

[pbaldy]

You got carried away with the quotes. Try

strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" & Me!lstpayment & "'"

 

[Jsak]

You got carried away with the quotes. Try

strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" & Me!lstpayment & "'"

That worked perfectly thank you .

One thing i just realized is, if a person doesn't want to include one of the list boxes it will never return any results. (i.e. they only want to know payment type and store name, but not buyer or keyword).

What syntax would i use to specify if nothing is selected, to not filter based off that list box?

 

[pbaldy]

You can build your string conditionally. I presume these listboxes are single select, so this type of thing:

If Me.lstpayment.ItemsSelected.Count = 1 Then
  strFilter = strFilter & "[payment_type] = '" & Me!lstpayment & "' And "
End If

At the end you trim off the last 5 characters to get rid of the trailing " And ".

 

[Jsak]

all of the list boxes are single select. the keyword list-box is part of a multi-value field however.

 

[Jsak]

I have a need to make the lstkeyword listbox multi-selectable. I did some research and came up with the following code for it, but i can't seem to get it to work correctly. I get a error of:

run time error '13'
type missmatch

and the debugger is pointing to these 2 lines of code

strKeywords = strKeywords & "'" & _
Me!lstkeyword.ItemData(varSelected & "', ")


This is the code that i am using for the multi-select list box.

If Me.lstkeyword.ItemsSelected.Count >= 1 Then
For Each varSelected In Me!lstkeyword.ItemsSelected
strKeywords = strKeywords & "'" & _
Me!lstkeyword.ItemData(varSelected & "', ")
Next
strKeywords = Left(strKeywords, Len(strKeywords) - 2)
strFilter = strFilter & "[keywords] IN (" & strKeywords & ") And "
End If


do i have a ' in the wrong place?

 

[pbaldy]

Well, you have the bit adding the trailing apostrophe and comma inside the parentheses intended for the ItemData argument.

 

[Jsak]

pbaldy - thanks again for the help. One bug i ran into is if a keyword isn't selected in the listbox

    If Me.lstname.ItemsSelected.Count = 1 Then
    strFilter = strFilter & "[purchased_by] = '" & Me!lstname & "' And "
  End If
   
  If Me.lstpayment.ItemsSelected.Count = 1 Then
    strFilter = strFilter & "[payment_type] = '" & Me!lstpayment & "' And "
  End If
   
  If Me.lststore.ItemsSelected.Count = 1 Then
    strFilter = strFilter & "[store_name] = '" & Me!lststore & "' And "
  End If
   
  If Me.lstkeyword.ItemsSelected.Count = 1 Then
    strFilter = strFilter & "[keywords] = '" & Me!lstkeyword & "'"
  End If
   
  DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

I’m 90% sure that it is because if a keyword isn’t selected that the final item which is selected has an “’ And ‘” at the end and needs a “’” so it will put the information in the report. If I have a keyword selected all the filters and reports come out perfectly.

What would i need to do to resolve this issue?

 

[pbaldy]

You are exactly correct. Numerous ways around it. Some would put the " And " at the end of all of the strings, including the last one, and then trim off the last 5 characters. Some would test for the last 5 characters being " And " and trim them off. You trim them like so:

strFilter = Left(strFilter, Len(strFilter) - 5)