Add Multiple Criteria to Same field in a Query

[bconner]

I have a query whose Criteria is linked to a Form. Specifically when someone selects a Location and pushes a Button the Query will run and only pull thru the Location that was selected. So far so good, now I would like to add one additional piece of criteria to the same field that if No Location is chosen it will pull for all Locations. Below is what I have tried but it doesn't work.

IIf(IsNull([Forms]![FrmReporting]![Combo_RefLocName]),"*",[Forms]![FrmReporting]![Combo_RefLocName])

 

[MStef]

Try next:
In query criteria put the code.
Like [Forms]![FrmReporting]![Combo_RefLocName] & "*"

 

[bconner]

Hi MStef,
I tried it but it didn't work. It pulled so long as I selected a location but if I left it blank it pull blank instead of all entries.

 

[rockyjr]

What I think you want is something like this:

WHERE (isnull(forms!FrmReporting![Combo_RefLocName]) Or TableName.TableFieldName1 Like "*" & forms!FrmReporting![Combo_RefLocName] & "*") 
And (isnull(forms!FrmReporting!FormFieldName2) Or TableName.TableFieldName2 Like "*" & forms!FrmReporting!FormFieldName2 & "*")

keep the "*" & and & "*" if you want everything plus whatever...
Example... a title.... if you enter a word.. it will get everything that has that word in it plus whatever...

if it is a combo box... you probably dont need it.

Oh ya... I forgot... do this in the SQL view.

 

[Brianwarnock]

Visit this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian

 

[bconner]

Thank you all for your responses they are all very useful and I learned a lot..... I appreciate your help...