asked on

Query crireria

I normally use this criteria to get a query to filter the records if there is a value in form field and ignore it if not plus any records in the query that have no values in the criteria field

Like nz([Forms]![Doors]![E],"*") Or Is Null

But now I need to say if there is a value in the form field I want all the records with values greater than the form field value plus any records in the query that have no values in the criteria field

Like nz(>=[Forms]![Doors]![E],"*") Or Is Null

But I cannot get it to work

mbizup

Try this:

>= nz([Forms]![Doors]![E], -999)

(-999 is some arbitrary value that is lower than any 'reasonable' value in your data)

Derek Brown

ASKER

The problem is the null values

I have just done this in a query grid field to return a zero if there is a null but it appears that the criteria runs before the selection below. So it will convert nulls to zeros but after the criteria has already eliminated the nulls.

A: IIf(Mid([AcousticRating],3,4)>0,Mid([AcousticRating],3,4),0)

Derek Brown

ASKER

Which means that my idea to make your solution work did not work.

So your idea is brilliant if I did not have nulls

mbizup

Perhaps this:

>= nz([Forms]![Doors]![E], -999) OR IS NULL

If that doesn't help, post the SQL and some sample data.

Derek Brown

ASKER

The problem is the nulls. How do I get this greeter than criteria and also get the Nulls

Derek Brown

ASKER

This was the solution

>=nz([Forms]![Doors]![K],"*") Or Is Null Or nz([Forms]![Doors]![K],"*")

I have no idea how it works but it does so!

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Derek Brown

ASKER

Wicked!!

Thank you

Derek