IIF to include Null Values from Table

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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
I think it needs to be

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

Kelvin
Ernest GroggSecurity Management InfoSec

Author

Commented:
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....
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Try changing the ANDS to ORs
Database and Application Developer
Commented:
Note:
null values  is no longer is correct terminology. Now it is referred to as null mark. Null is not a value. It cannot have a value. One null is never equal to another null.
WHERE Nz(MGNameAddressPhone.FName,"") Like "*" & [Forms]![frmRecordsEdit]![FName] & "*"
             OR Nz(MGNameAddressPhone.LName,"") Like "*" & [Forms]![frmRecordsEdit]![LName] & "*"
             OR Nz(MGNameAddressPhone.Gender,"") Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*"
             OR NZ(MGNameAddressPhone.SSN,"") Like "*" & [Forms]![frmRecordsEdit]![Gender] & "*"

Open in new window

And in English writing translates to OR in SQL.

Of course applying functions on the right side is not good idea and it makes query nonsargable.
Ryan ChongSoftware Team Lead

Commented:
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 EghtebasDatabase and Application Developer

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial