Link to home
Start Free TrialLog in
Avatar of AbacusInfoTech
AbacusInfoTech

asked on

Trouble getting wildcard to work using IIF Like Search

Hello Experts.

On my FrmMain I have a combo box that acts as the filter for a report. It's rowsource is:

SELECT name from tboffice WHERE (((tbloffice.isplanning)=True) AND ((tbloffice.iscurrent)=True)) UNION Select "<All>" As name from tbloffice ORDER BY name;

This gives me the list of offices I want and adds <All> at the top of this list. The idea is that a user can run the report for an individual office, or all of them.

(I got this from https://www.experts-exchange.com/questions/20793837/Using-All-in-a-combo-box.html)

The query that the report is based on includes the following as a criteria:

IIf([Forms]![FrmMain]![cboReportOfficePicker]="<All>",([tbloffice].[name]) Like "*",[Forms]![FrmMain]![cboReportOfficePicker])

The reports work perfectly if I choose an office, but I get NO records returned when <All> is selected, so I think I need to change my:

Like "*"

But to what?

I have played around with specifiying an actual office instead of Like "*" and I do get results, so Access seems to 'understand' when <All> is selected.

Haylp!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mr_Peerapol
Mr_Peerapol

Try this:

WHERE ([Forms]![FrmMain]![cboReportOfficePicker]="<All>" OR [tbloffice].[name])=[Forms]![FrmMain]![cboReportOfficePicker])
Try not passing anything for the True part:

IIf([Forms]![FrmMain]![cboReportOfficePicker]="<All>","",[Forms]![FrmMain]![cboReportOfficePicker])
Avatar of AbacusInfoTech

ASKER

Wow, what a response - and all different. I'll try them all and be back soon.
AngelIII - Worked First Time!

Mr Peerapol - There is a problem with your brackets. Feel free to re-post and i'll try again with your solution.

LSMConsulting
No, doesn't work. I get no results.

So far AngelIII wil get the points but if Mr Peerapol wants to try once more then I might do a split.
:-)

WHERE ([Forms]![FrmMain]![cboReportOfficePicker]="<All>" OR [tbloffice].[name]=[Forms]![FrmMain]![cboReportOfficePicker])
Mr P,

Undefined function 'WHERE' in expression.