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?
Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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
0
Kelvin SparksCommented:
I think it needs to be

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

Kelvin
0
Ernest GroggSecurity Management InfoSecAuthor 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....
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kelvin SparksCommented:
Try changing the ANDS to ORs
0
Mike EghtebasDatabase and Application DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.