I would rather have thought to use:
...
WHERE ((dbo_DS_invester_contact.DS_invest_contact_last IS NULL)
OR (dbo_DS_invester_contact.DS_invest_contact_last Like
[Forms]![frmSearch]![qLastName] & "*"))
AND ((dbo_DS_invester_contact.DS_invest_mobile IS NULL )
OR (dbo_DS_invester_contact.DS_invest_mobile Like
[Forms]![frmSearch]![qmobile] & "*"))
AND ((dbo_DS_invester_contact.DS_invest_email IS NULL)
OR (dbo_DS_invester_contact.DS_invest_email) Like
[Forms]![frmSearch]![qemail] & "*"))
ORDER BY dbo_DS_invester_contact.DS_invest_contact_last;
since for each field, your want it either NULL, either if its value is
like its associated combo box.
Your statement would return the record if the THREE VALUES are all three
NULLS or if they are all like their associated combo box.
Not the same thing.
But if it is what you want...
The statement:
WHERE Nz (dbo_DS_invester_contact.DS_invest_contact_last Like
[Forms]![frmSearch]![qLastName] & "*", true)
AND Nz(dbo_DS_invester_contact.DS_invest_mobile Like
[Forms]![frmSearch]![qmobile] & "*", true)
AND Nz(dbo_DS_invester_contact.DS_invest_email) Like
[Forms]![frmSearch]![qemail] & "*", true)
should be equivalent to the first one, though.
Vanderghast, Access MVP
Lez said:
Ok I think I have got is sorted now :
SELECT dbo_DS_invester_contact.DS_invest_contactID,
dbo_DS_invester_contact.DS_invest_contact_first,
dbo_DS_invester_contact.DS_invest_contact_last,
dbo_DS_invester_contact.DS_invest_mobile,
dbo_DS_invester_contact.DS_invest_email
FROM dbo_DS_invester_contact
WHERE (((dbo_DS_invester_contact.DS_invest_contact_last) Like
[Forms]![frmSearch]![qLastName] & "*") AND
((dbo_DS_invester_contact.DS_invest_mobile) Like
[Forms]![frmSearch]![qmobile] & "*") AND
((dbo_DS_invester_contact.DS_invest_email) Like
[Forms]![frmSearch]![qemail] & "*")) OR
(((dbo_DS_invester_contact.DS_invest_contact_last) Is Null) AND
((dbo_DS_invester_contact.DS_invest_mobile) Is Null) AND
((dbo_DS_invester_contact.DS_invest_email) Is Null))
ORDER BY dbo_DS_invester_contact.DS_invest_contact_last;
Thanks for your help
vanderghast said:
Do you use those fields in OTHER criteria? Most operations involving
NULL returns NULL, which is not true, so the overall criteria is likely
to not be kept (if they are AND-ed).
Can you post the whole SQL statement (as you can get it in SQL view) ?
Vanderghast, Access MVP
I tried that suggestion but had no luck with it, still did not filter
any records if there were null values.
Edit the query in SQL view.
Spot the WHERE clause. Spot the:
fieldName LIKE FORMS![frmSearch]![qlastName] & "*"
and replace it with:
Nz( fieldName LIKE FORMS![frmSearch]![qlastName] & "*", true)
Do the same for the two other fields, if requiered.
Note that doing it that way, a NULL stored in the db will always match
anything.
Vanderghast, Access MVP
Hi Guys,
I have created a search facility to filter for any:
Last Name, Tel or Email
My result form has a query for each field serach on:
Like[Forms]![frmSearch]![qlastname]&"*"
Like[Forms]![frmSearch]![qtel]&"*"
Like[Forms]![frmSearch]![qemail]&"*"
This only works only if all feilds in the table searched has a value,
what I need to do is give a result for ANY of the values.
Any suggestions?
Thanks Lez
