Thank you so much. I was ready to start pounding my head against the wall...hoping to shake my memory up. :). I just want to say that you guys are the best. I am so glad that I found you. |
From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>;
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>;
Subject: RE: [MS_AccessPros] Queries with Like in criteria
Sent: Tue, Jun 17, 2014 10:16:24 PM
|
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: "k2j1203@yahoo.com" <k2j1203@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar