We help IT Professionals succeed at work.

Query filter

Derek Brown
Derek Brown asked
on
What is the difference between:
Like Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4) & "*" and
Like nz(Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4) & "*","*")

How would I get a different result
What I would like is:
Like [Forms]![ProjectForm]![Details].[Form]![FireRating]  or isnull.

What I'm trying to acheive is, filter records by the exact content of the form field unless the field is empty (Is null and "") in which case show all the records.
Comment
Watch Question

CERTIFIED EXPERT

Commented:

You always can add separate field to your query equal to

Isnull([Forms]![ProjectForm]![Details].[Form]![FireRating])

with OR criteria True

to your main criteria

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

You would want:


[Forms]![ProjectForm]![Details].[Form]![FireRating] Is Null or   Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4) & "*"


Jim.

Author

Commented:
Hi Jim

I'm using access 2002 but that does not work for me I have to use

On this occasion I don't need the Left and ,4)

So finally this works.  Like nz(([Forms]![ProjectForm]![Details]![FR]),"*")

I can't quite understand why I can't use Like [Forms]![ProjectForm]![Details]![FR]) Or IsNull

Hi Als315 could not get that to work either
CERTIFIED EXPERT
Commented:

Look at sample DBDB29174264.mdb

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:

<< I can't quite understand why I can't use Like [Forms]![ProjectForm]![Details]![FR]) Or IsNull>>


 Because you only have part of the expression there.   But you need it in this form:


   Like <this value>  Or   <Something>  Is null


Jim.

Author

Commented:
Thanks Both

Author

Commented:
Thanks Als315 Git I can see what I was doing wrong.

Thanks Jim Got it