asked on 7/31/2015

IIF to include Null Values from Table

Hello,

I can't seem to get the statement for my query....

IIF(IsNull Like "*" & Forms!frmForm!myfield & "*"

I am normally using the Like statement across the board but I want to make sure that if the field in the table is Null or empty I want to still return all the records for that field. If there is a null field in the table it leaves that record out of the query....I want to make sure I include that record.

Don't know what to parts for the true and false would be?

Microsoft Access SQL

Last Comment

Mike Eghtebas

7/31/2015

Mike Eghtebas

7/31/2015

IIF(Nz(FieldName,'')<>'',"FieldInNotNull", "FieldIsNull")

I am not able to follow what your are trying to accomplish, but I detect empty space or null value like above.

Here:
Nz(FieldName,'') return '' if the it is '' or null

Then it compares <> '' if true then it has a value otherwise it is either '' or null.

Mike

Kelvin Sparks

7/31/2015

I think it needs to be

IIF(your field LIKE .... OR isNULL(your field),List what you want, the rest)

Kelvin

Ernest Grogg

7/31/2015

ASKER

What I have is a SQL:

SELECT MGNameAddressPhone.FName, MGNameAddressPhone.LName, MGNameAddressPhone.Gender, MGNameAddressPhone.SSN
FROM MGNameAddressPhone
WHERE (((MGNameAddressPhone.FName) Like "*" & [Forms]![frmRecordsEdit]![FName] & "*") AND ((MGNameAddressPhone.LName) Like "*" & [Forms]![frmRecordsEdit]![LName] & "*") AND ((MGNameAddressPhone.Gender) Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*") AND ((MGNameAddressPhone.SSN) Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*"));

Open in new window

What happens if is the field: SSN has any null values the Like Statement doesn't include that in the list. it removes those that have null values in the table

What I have is say 34 records in the Table

15 records with a first name of "TEST" but out of those 15, 5 have SSN's listed...so only 5 records show up in the query....I need all 15 to show in the results if I am searching on "TEST" for the First Name....

Kelvin Sparks

7/31/2015

Try changing the ANDS to ORs

ASKER CERTIFIED SOLUTION

Mike Eghtebas

7/31/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Ryan Chong

7/31/2015

based on my understanding, is this what you trying to achieve?

SELECT MGNameAddressPhone.FName, MGNameAddressPhone.LName, MGNameAddressPhone.Gender, MGNameAddressPhone.SSN
FROM MGNameAddressPhone
WHERE (
((MGNameAddressPhone.FName) Like "*" & [Forms]![frmRecordsEdit]![FName] & "*") AND ((MGNameAddressPhone.LName) Like "*" & [Forms]![frmRecordsEdit]![LName] & "*") AND ((MGNameAddressPhone.Gender) Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*") AND 

(
  ((MGNameAddressPhone.SSN) Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*") OR       (MGNameAddressPhone.SSN IS NULL)
)

);

Open in new window

Mike Eghtebas

7/31/2015

BTW, I need to explain something about my earlier statement "And in English writing translates to OR in SQL."

This applies sometimes. Not necessarily always.

Example when it applies.
Requirement: Get Address for Jack and Bob from Employees table:
Select Address From Employees
Where Name = 'Jack' or Name ='Bob'

Example When doesn't apply.
I don't know yet. I have to investigate.

Mike