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
DatabaseDekAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
That last nz looks redundant.

Try this:

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

The issue with the syntax in your original post is that the >= needed to be *outside* of the NZ.

Regarding the "*" versus -999, are you dealing with TEXT fields rather than numeric fields?  If so the "*" behaves similarly to the -999 (which would work for numeric fields).  It is simply an arbitrary low value, which alphabetically comes before any expected values in your data.
0
 
mbizupCommented:
Try this:

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


(-999 is some arbitrary value that is lower than any 'reasonable' value in your data)
0
 
DatabaseDekAuthor Commented:
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)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

So your idea is brilliant if I did not have nulls
0
 
mbizupCommented:
Perhaps this:

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

If that doesn't help, post the SQL and some sample data.
0
 
DatabaseDekAuthor Commented:
The problem is the nulls. How do I get this greeter than criteria and also get the Nulls
0
 
DatabaseDekAuthor Commented:
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!
0
 
DatabaseDekAuthor Commented:
Wicked!!

Thank you

Derek
0
All Courses

From novice to tech pro — start learning today.