Link to home
Start Free TrialLog in
Avatar of Ernest Grogg
Ernest Grogg

asked on

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?
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

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
Avatar of Kelvin Sparks
I think it needs to be

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

Kelvin
Avatar of Ernest Grogg
Ernest Grogg

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....
Try changing the ANDS to ORs
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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