I am using Access 2003 and want to have a query return results based
on the value a text box in a hidden form. Depending on the value in
the text box the result should be to show a filtered selection or show
all records.

If Sales is the value in the text box, then Sales staff could view
only records for the Sales department and so on with Service, Research
departments. However if the value is Director then I want it to show
all records.

I am using this but it doesn't work when it comes to showing all
records

IIf([Forms]![frmHidden]![txtAgency]="Director","*",[Forms]!
[frmHidden]![txtAgency])

Most grateful for your assistance.

VT

My advice would be to use

[Forms]![frmHidden]![txtAgency] Or
[Forms]![frmHidden]![txtAgency]="Director"

although you could also just put Like in front of what you've currently got:

Like
IIf([Forms]![frmHidden]![txtAgency]="Director","*",[Forms]![frmHidden]![txtAgency])

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)



"Vet Tech" <senditontome (AT) gmail (DOT) com> wrote

On 16/07/2010 12:49:40, "Douglas J. Steele" wrote:
Try putting another field on your form TxtAgencyRelay with the ControlSource
'=IIf([TxtAgency]) = "Director", "*"[TxtAgency])'

and your query criteria
Like [Forms]![frmHidden]![txtAgencyRelay]

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

That won't work Phil, if only because the IIf statement is invalid syntax
(looks as though you left out a comma). Even if you correct the syntax,
though, I think you'll find it won't do what's asked for.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)

On 19/07/2010 03:05:19, "Douglas J. Steele" wrote:
Hi Douglas
Typo error Should be '=IIf([TxtAgency]) = "Director", "*",[TxtAgency])'

I use a similar routine to select surnames based on an initial letter
selected from a grid where I load that letter into into a field on the form,
or if I choose the "All" option it loads an * into the field. Works fine

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote


My apologies. I see now what you're trying to do.

I don't see any advantage, though, of adding an additional control to the
form vs. having the query do the work.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

On 19/07/2010 12:01:09, "Douglas J. Steele" wrote:
Apology accepted.

You're probably right about it being a complex way of doing things, but that
method works, and the OP seemed to be having dificulties with his query

Phil

It appears it may have been a Null problem.

I used the Nz thingie and it has worked out OK so far.

This is what I put in:-

Like Nz([forms]![frmHidden]![txtAgency],"") & "*"

Many thanks anyway for your suggestions.

VT