Handling NULL with IIF statement

[robbeh]

I have a query that takes the first three digits of a 6 digit postal code as a Criteria. However, if no digits are added I want all records to return as if there was no criteria.

I am using an IIF statement with a Is Not Null but the problem is, if it is NULL I can't return all records.

Here is my criteria:

Like IIf([Forms]![frmOptions]![PostalCode] Is Not Null,[Forms]![frmOptions]![PostalCode] & "*","*")

I have tried:

Like IIf([Forms]![frmOptions]![PostalCode] Is Not Null,[Forms]![frmOptions]![PostalCode] & "*",([tblPatient].[Postal]) Like "*" Or ([tblPatient].[Postal]) Is Null)
Like IIf([Forms]![frmOptions]![PostalCode] Is Not Null,[Forms]![frmOptions]![PostalCode] & "*",([tblPatient].[Postal]) Like "*")
Like IIf([Forms]![frmOptions]![PostalCode] Is Not Null,[Forms]![frmOptions]![PostalCode] & "*",([tblPatient].[Postal]) Is Null)

As you can guess, the postal code comes from the tblPatient.

Am I missing something? It works well with the "*", it just doesn't capture if no postal code is added.

Thanks!

[June7]

Like [Forms]![frmOptions]![PostalCode] & "*"

Should return all records if no input in the control.

Do you want to exclude records where field is null?

Like [Forms]![frmOptions]![PostalCode] & "*" AND Not Is Null

[robbeh]

Hi June,

Thanks for the response. I have tried simply using:
Like [Forms]![frmOptions]![PostalCode] & "*"

And it still only returns the records where Postal Code is not null.

If I leave the criteria blank, it returns all records including records where postal code is null. This is the functionality I want but I also want to be able to search using part of a postal code.

Like [Forms]![frmOptions]![PostalCode] & "*"

Should return all records if no input in the control.

Do you want to exclude records where field is null?

Like [Forms]![frmOptions]![PostalCode] & "*" AND Not Is Null

[June7]

I am confused. Some records don't have postal code? You want to retrieve those records regardless of parameter input?

Like [Forms]![frmOptions]![PostalCode] & "*" Or Is Null

[robbeh]

So close! Ok, let me break it down a little better.

I want to generate a report using a form with a text box - PostalCode to take on criteria

If I enter nothing into the PostalCode text box --> I want all records to return that have a postal code and that are null for postal code (basically every record available)
If I enter a partial postal code (Example L6H) --> I want only the records with L6H* to return and NONE of the null ones

Like [Forms]![frmOptions]![PostalCode] & "*" Or Is Null ----> works perfectly on it's own. It returns postal code records and ones where the postal code is null. Issue is, if I put in a postal code, it returns the similar postal codes (waht I want) AND null. At this point I don't want null.

So I figured I had to use an IIF statment:

Like IIf([Forms]![frmOptions]![PostalCode] Is Not Null,[Forms]![frmOptions]![PostalCode] & "*",[Forms]![frmOptions]![PostalCode] & "*" Or Is Null)

So IF the PostalCode textbox is not empty --> Like [Forms]![frmOptions]![PostalCode] & "*" (this works perfectly on it's own and if I type in a partial postal code)
If the PostalCode textbox IS empty --> Like [Forms]![frmOptions]![PostalCode] & "*" Or Is Null (on it's own as criteria this works, but for whatever reason when I put it in an IIF statement it doesnt).

Thanks again June, hopefully this makes a bit more sense.

I am confused. Some records don't have postal code? You want to retrieve those records regardless of parameter input?

Like [Forms]![frmOptions]![PostalCode] & "*" Or Is Null

[robbeh]

In addition, I tried using IIf ....Is Null.... as well. No avail. One thing I have noticed, is whenever I put this statement into an IIF

[Forms]![frmOptions]![PostalCode] & "*" Or Is Null

It turns into

[tblPatient].[Postal]=[Forms]![frmOptions]![PostalCode] & "*" Or Is Null

I think that might be why it works when it's not an IIf but as soon as I put it into an IIf it fails.

[June7]

Maybe create a field with expression in query that handles the Nulls and returns an underscore:

PC: IIf([PostalCode] Is Null, "_", [Postal Code])

Apply filter criteria to that constructed field.

Presuming user will never input an underscore as search parameter and an actual PostalCode does not contain underscore.

[robbeh]

Thanks again June.

I figured out the solution - I couldn't put in a IIf statement so I did the following.

In the Postal Code criteria: Like [Forms]![frmOptions]![PostalCode] & "*"
Then create another field: [Forms]![frmOptions]![PostalCode] and in the Or criteria add Is Null

I actually put the whole string in the Postal Code criteria and Access broke it out:
Like [Forms]![frmOptions]![PostalCode] & "*" OR [Forms]![frmOptions]![PostalCode] Is Null

Hopefully this helps anyone with the same issue. Not to figure out how to do that for two text fields on the form.