Prev: Simple Word Doc Viewer
Next: NameMap = Begin
From: Vet Tech on 16 Jul 2010 03:01 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 16 Jul 2010 07:49 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 16 Jul 2010 10:44 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 18 Jul 2010 22:05 "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 19 Jul 2010 03:36
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 |