Select ALL from an unbound form for query parameters

[Stig]

Hi,

Any help on this one will be greatly appreciated as I have spent too
long banging my head against the screen trying to get it sorted.
Basically I would like to have a select all records option in a combo
box that is used in an unbound form for my report parameter. I have
tried to explain it in further detail below: -

In my DB I have numerous forms are used to input details into a table
(TblMasterTable).

In the input forms I have combo boxes that are used to populate fields
in the master table. The combo boxes in the forms are bound to tables
(e.g. TblComboHRAreas).

I have reports set-up that run from queries that use the data in the
master table. The parameters for the queries are linked to an unbound
form (FrmParam) that has the following fields on it: -

StartDate
EndDate
HRArea (Combo box that uses the records in TblComboHRAreas for its
selection)
Team (Combo box that uses the records in TblComboTeam for its
selection)

I have the following expressions in the queries for the parameter
fields:

HRArea (expression): - [Forms]![FrmParam]![HRArea]

The reports work fine with you select a HR Area from the combo box.
The problem I am experiencing is to select all records from the combo
source (TblComboHRAreas).

I have tried replacing the above expression with the following: -

Like IIf([Forms]![FrmParam]![HRArea]=0,"*",[Forms]![FrmParam]![HRArea])

I have also tried replacing the 0 with isnull, "", nil etc. with no
success as the problem seem to be that when the wildcard * is does not
extract all records instead I get no records.

Thats about it. Thanks in advance if you can help solve my problem.

[Geoff G]

Try this

Change the criteria in the query to

[Forms]![FrmParam]![HRArea] OR [Forms]![FrmParam]![HRArea] Is Null

You can use this technique for multiple fields in your query but the
query design gets complicated quickly. I find that for simplicity, a
set of cascading queries works quite well so that each query filters a
bit of the info and is used to build the next one.

Geoff

[Stephen Pepper]

Hi Geoff,

Thanks for your suggested solution. It was simple to implement and
worked perfectly, it will also go along way towards helping my sanity.

Thanks again and I hope you enjoy the New Year celebrations.

Cheers,

Stig
*** Sent via Developersdex http://www.developersdex.com ***

[Geoff G]

Nice to know it works OK. I found this one at

http://www.mvps.org/access/

It's good place to look for help.

Happy New Year