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?
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?
I think it needs to be
IIF(your field LIKE .... OR isNULL(your field),List what you want, the rest)
Kelvin
IIF(your field LIKE .... OR isNULL(your field),List what you want, the rest)
Kelvin
ASKER
What I have is a SQL:
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....
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] & "*"));
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
)
);
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
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
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