Open Form filtered by one or two combo boxes

[jamlaw]

Hi

I have a report that gets filtered by a form with 2 combo boxes on. If I select data from both the combo boxes the report works fine. I'd like to get it to have the ability to select data from 1 or 2 of the combo boxes. The code is;

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![Text91])and (Performance.Gender=[Forms]![Performance Dynamic Report Builder]![Text94])"

Tried everywhere but can't get it to work.

Thanks

 

[Isskint]

hi Jamlaw

Build the WHERE criteria in a string, checking each combobox has an entry. try this?

Dim StrRptWhere as String

If nz([Text91])<>0 then
StrRptWhere = "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![Text91])"
end if

If nz([Text94])<>0 then
if nz(StrRptWhere) = 0 then
StrRptWhere = "(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![Text94])"
else
StrRptWhere=StrRptWhere & " AND " &"(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![Text94])"
end if
end if
'check StrRptWhere contains data
If NZ(StrRptWhere)=0 then
msgbox "No criteria selected",vbInformation,"Title"
exit sub
end if

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , StrRptWhere

 

[jamlaw]

thanks it work great. But only if the first combo box is used and the second is empty and not if its the other way round.

Also if I wanted to add more combo boxes ie [grade] how would I go about adding that as I see the code is 'If', 'then' and the 'and' functions.

Really appreciate your help.