Karen,
The Like * will not match a field value that is NULL. NULL is somewhat comparable to "unknown". Access queries can't compare anything to an unknown value. You can concatenate a zero-length-string to the field value to convert it from NULL to "" and it should work:
SELECT qryUnionSearch.[Event Name], qryUnionSearch.[Host Organization Name], qryUnionSearch.Address, qryUnionSearch.[Org Contact Name], qryUnionSearch.[FHN or CCAI Coordinator], qryUnionSearch.Status, qryUnionSearch.[Event Date]
FROM qryUnionSearch
WHERE [Event Name] & "" Like "*" & [Forms]![Search Form]![ActName] & "*" AND
[Host Organization Name] & "" Like "*" & [Forms]![Search Form]![OrgName] & "*" AND
Address & "" Like "*" & [Forms]![Search Form]![ActivityAddr] & "*";
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
Hello all. I feel that I should know that answer to the following question, but I can't seem to dig it out of my memory. So, here's what I am trying to do.
I have a form with unbound controls in order to search for records. I have four different search options: Search by organization name, activity name, address or activity date. I have a select query that runs, well I'm trying to run, based off of the information the user enters.
I thought that all I needed was to add Like statements as the criteria in the query. However, that doesn't seem to be working correctly. Here's the sql for my query.
SELECT qryUnionSearch.[Event Name], qryUnionSearch.[Host Organization Name], qryUnionSearch.Address, qryUnionSearch.[Org Contact Name], qryUnionSearch.[FHN or CCAI Coordinator], qryUnionSearch.Status, qryUnionSearch.[Event Date]
FROM qryUnionSearch
WHERE (((qryUnionSearch.[Event Name]) Like "*" & [Forms]![Search Form]![ActName] & "*") AND ((qryUnionSearch.[Host Organization Name]) Like "*" & [Forms]![Search Form]![OrgName] & "*") AND ((qryUnionSearch.Address) Like "*" & [Forms]![Search Form]![ActivityAddr] & "*"));
I thought that having the astericks in the Like statements would select the records with the information from the search form or if that field was left blank.
Hopefully I explained this well.
Thanks.
Karen
The Like * will not match a field value that is NULL. NULL is somewhat comparable to "unknown". Access queries can't compare anything to an unknown value. You can concatenate a zero-length-string to the field value to convert it from NULL to "" and it should work:
SELECT qryUnionSearch.[Event Name], qryUnionSearch.[Host Organization Name], qryUnionSearch.Address, qryUnionSearch.[Org Contact Name], qryUnionSearch.[FHN or CCAI Coordinator], qryUnionSearch.Status, qryUnionSearch.[Event Date]
FROM qryUnionSearch
WHERE [Event Name] & "" Like "*" & [Forms]![Search Form]![ActName] & "*" AND
[Host Organization Name] & "" Like "*" & [Forms]![Search Form]![OrgName] & "*" AND
Address & "" Like "*" & [Forms]![Search Form]![ActivityAddr] & "*";
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
Hello all. I feel that I should know that answer to the following question, but I can't seem to dig it out of my memory. So, here's what I am trying to do.
I have a form with unbound controls in order to search for records. I have four different search options: Search by organization name, activity name, address or activity date. I have a select query that runs, well I'm trying to run, based off of the information the user enters.
I thought that all I needed was to add Like statements as the criteria in the query. However, that doesn't seem to be working correctly. Here's the sql for my query.
SELECT qryUnionSearch.[Event Name], qryUnionSearch.[Host Organization Name], qryUnionSearch.Address, qryUnionSearch.[Org Contact Name], qryUnionSearch.[FHN or CCAI Coordinator], qryUnionSearch.Status, qryUnionSearch.[Event Date]
FROM qryUnionSearch
WHERE (((qryUnionSearch.[Event Name]) Like "*" & [Forms]![Search Form]![ActName] & "*") AND ((qryUnionSearch.[Host Organization Name]) Like "*" & [Forms]![Search Form]![OrgName] & "*") AND ((qryUnionSearch.Address) Like "*" & [Forms]![Search Form]![ActivityAddr] & "*"));
I thought that having the astericks in the Like statements would select the records with the information from the search form or if that field was left blank.
Hopefully I explained this well.
Thanks.
Karen
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar