No records returned for query based on Unbound Form item 1

[Stoffman]

I have an unbound drop down list form item, which I am using to query the data. The User can either choose Debit or Credit or leave the option blank.

This criteria works perfectly ([Forms]![form name]![form field]), but only if the user chooses one of the options. If they leave this field blank no records are returned, whereas I want all records to be returned.

I have tried:

IIf ([Forms]![form name]![form field]is null, like "*", [Forms]![form name]![form field]) as the criteria in the query but this gives the same result as above

Any Ideas?

 

[MA04]

Hi,

Try this:
Like "*" & IIf([Forms]![form name]![form field]="","",[Forms]![form name]![form field]) & "*"

HTH,
M-.

 

[Stoffman]

Marvellous, I had to change the drop down list to have an "" option but then it run like a beauty. How does this work?

 

[MA04]

Alot of the credit for the code should go to dRahme who showed me something similar to a similar problem i had like yours.

What the code does is it sets a wildcard entry "*" if there is not an entry already ie. if it is null. So user has the option whether to enter an entry or not.

M-.

 

[PHV]

Without any change to the drop down list:
Like '*' & [Forms]![form name]![form field] & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stoffman]

This is all working great. But what about if a field is either A, B or Null, and the user can choose A or B or Nothing, but if they choose nothing I want to display everything even Null values at the moment if the user doesn't choose anything it only displays All Records which have something and ingores all the null values. HHmmmm. Any Ideas

 

[PHV]

One way:
WHERE (yourField=[Forms]![form name]![form field] Or [Forms]![form name]![form field] Is Null)
Another way:
WHERE Nz(yourField,"")=Nz([Forms]![form name]![form field],"")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244