From: Vet Tech on
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
From: Douglas J. Steele on
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(a)gmail.com> wrote in message
news:17b12c3c-f062-4c33-a91a-b672fcc1bdd3(a)d16g2000yqb.googlegroups.com...
>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

From: Phil on
On 16/07/2010 12:49:40, "Douglas J. Steele" wrote:
> 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])
>

Try putting another field on your form TxtAgencyRelay with the ControlSource
'=IIf([TxtAgency]) = "Director", "*"[TxtAgency])'

and your query criteria
Like [Forms]![frmHidden]![txtAgencyRelay]
From: Douglas J. Steele on
"Phil" <phil(a)stantonfamily.co.uk> wrote in message
news:GOqdnTh2BuDN7d3RnZ2dnUVZ8nydnZ2d(a)brightview.co.uk...
> On 16/07/2010 12:49:40, "Douglas J. Steele" wrote:
>> 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])
>>
>
> Try putting another field on your form TxtAgencyRelay with the
> ControlSource
> '=IIf([TxtAgency]) = "Director", "*"[TxtAgency])'
>
> and your query criteria
> Like [Forms]![frmHidden]![txtAgencyRelay]

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!)




From: Phil on
On 19/07/2010 03:05:19, "Douglas J. Steele" wrote:
> "Phil" <phil(a)stantonfamily.co.uk> wrote in message
> news:GOqdnTh2BuDN7d3RnZ2dnUVZ8nydnZ2d(a)brightview.co.uk...
>> On 16/07/2010 12:49:40, "Douglas J. Steele" wrote:
>>> 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])
>>>
>>
>> Try putting another field on your form TxtAgencyRelay with the
>> ControlSource
>> '=IIf([TxtAgency]) = "Director", "*"[TxtAgency])'
>>
>> and your query criteria
>> Like [Forms]![frmHidden]![txtAgencyRelay]
>
> 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.
>

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
 |  Next  |  Last
Pages: 1 2
Prev: Simple Word Doc Viewer
Next: NameMap = Begin