Saturday 20 August 2011

ACCESS - blank parameter queries - SOLVED

I have had sooo much trouble getting a parameter query to work in the ACCESS query grid.
Something seemingly so simple ....
When running a query I wanted to be prompted for whether the records should show those with a Checkbox field Ticked, eg (T)rue, Unticked (F)alse or Both Ticked and Unticked (All)
To cut directly to the chase, here is the query criteria which I finally got to work.

Note: The checkbox field being queried is called TaxClaim

IIf([Tax Claim? (T)rue, (F)alse or 'Enter' = All] Is Null,[TaxClaim],IIf([Tax Claim? (T)rue, (F)alse or 'Enter' = All] Like "T*",-1,0))
       If                                                     Then      Else

If Nothing is entered in the prompt dialog e.g. if just 'Enter' or 'OK' is pressed Then display everything in the [TaxClaim] field ... both Ticked and Unticked.

If anything is entered in the parameter dialog (preferably T, or True, or F, or False) the Else statement is executed.
Within the Else section is another nested IIf which checks if the entry is Like anything starting with T (Like "T*") if it is Then it sets the TaxClaim query criteria to -1 which equates to ticked checkboxes. If its anything Else which doesn't start with "T" (i.e. F or False) the TaxClaim query criteria is set to 0 which equates to Unticked checkboxes.

Key points:
The first and subsequent references to the one prompt must be identical
[Tax Claim? (T)rue, (F)alse or 'Enter' = All] 
or (a) subsequent prompt(s) appear(s) and the logic breaks

In the first ImmediateIf, the Is Null only seems to work the way shown. 
I originally tried to use it like IsNull([Tax Claim? ....]) ... that doesn't work in the query grid

The only thing which returned All records (both ticked and unticked) was the use of the Field Name (TaxClaim) in its referenced form (square brackets [TaxClaim])

No comments: