How to set criteria to everything including fields with nothing.

[L_E_O_N]

I am using the below statement in Criteria set the criteria to ANZTransType
on the form if not everything although what it is actually doing is if there
is nothing in ANZTransType its displaying everything except the fields which
have nothing in them.

I have tried a few variations and I guess I need to try and set the criteria
to be whats in ANZTransType otherwise nothing.

I cant seem to work out how to do this and would greatly appreciate any
assistance provided.

Leon

Like
IIf(Len([Forms]![frmANZ_UNMATCHED]![ANZTransType]>0),[Forms]![frmANZ_UNMATCHED]![ANZTransType],'*')

 

[Allen Browne]

Switch your query to SQL View (View menu, in query design.)

Locate the WHERE clause.

Set it up so it reads like this:
WHERE (([Forms]![frmANZ_UNMATCHED]![ANZTransType] Is Null)
OR ([SomeField] = [Forms]![frmANZ_UNMATCHED]![ANZTransType]))

That expression returns True if the text box on the form is null.
It also returns true if SomeField matches the value in the box.

The important thing is not to compare anything to the null if the text box
is null. That's why you can't just type something into the Criteria row
under your field.

 

[John Spencer]

Try Entering the following as the criteria in one "cell"

[Forms]![frmANZ_UNMATCHED]![ANZTransType] Is NULL or
=[Forms]![frmANZ_UNMATCHED]![ANZTransType]


When you close and save the query, Access will rearrange that in the
design view, but it will still work.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================