Search form based on option group option
Hi. I'm not a newbie to Access but not sure how to do this. I have one unbound textbox called 'text1" in the control. I want to search by multiple things based on the answer in an option group. For instance, the one search field and say 3 option group options based on a table or query. The 3 option groups are "search by ID", "search by name" or "search by city."
I want Access to be able to search the right field and pull up the correct results based on what the user has chosen. I think I saw this someplace in a sample Access database but I can't find it on the web anywhere. If no options are chosen, I want all records. If the first option is chosen of "search by ID" and they type in "12" in the textbox, I want everything with "12" to come up in the search results.
Thanks experts!
I want Access to be able to search the right field and pull up the correct results based on what the user has chosen. I think I saw this someplace in a sample Access database but I can't find it on the web anywhere. If no options are chosen, I want all records. If the first option is chosen of "search by ID" and they type in "12" in the textbox, I want everything with "12" to come up in the search results.
Thanks experts!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also... I generally use 'search forms' to filter data forms. I put all the unbound textboxes, combos, etc. for fields I may want to search. Then in code:
dim crit as string
if not isnull(me.txtbox1) then
crit = "Field1 = " & me.txtbox1
end if
if not isnull(me.txtbox2) then
if crit <> "" then crit = crit & " and "
crit = crit & "Field2 = " & chr(34) & me.txtbox2 & chr(34) ' --- chr(34) = "
end if
'--- and so on
docmd.openform YourForm,,, crit
Scott C
dim crit as string
if not isnull(me.txtbox1) then
crit = "Field1 = " & me.txtbox1
end if
if not isnull(me.txtbox2) then
if crit <> "" then crit = crit & " and "
crit = crit & "Field2 = " & chr(34) & me.txtbox2 & chr(34) ' --- chr(34) = "
end if
'--- and so on
docmd.openform YourForm,,, crit
Scott C
ASKER
Thank you for all the great responses. I have tried a couple of these and BillDenver's seems to work for me. I followed your instructions Bill. Now do I have to put this in the query criteria line:
Like "*" & [Forms]![Form1]![text1] & "*" for it to work? I can't seem to get it to work exactly right as of yet.
On the "onclick" of the button I am running Query1 then I guess it's looking at the after update?
Thank you mbizup and clarkscott for the suggestions. I will try those as well just so I understand everything.
Like "*" & [Forms]![Form1]![text1] & "*" for it to work? I can't seem to get it to work exactly right as of yet.
On the "onclick" of the button I am running Query1 then I guess it's looking at the after update?
Thank you mbizup and clarkscott for the suggestions. I will try those as well just so I understand everything.
ASKER
I think I may have it now. I put the LIKE statement in the "OR" instead of the "AND" criteria and it seems to be working now. Thanks guys for all your help!
Hi,
If your form is named Form1 then what is the recordsource? If it's a table then the code will work but the user would need to type in the City exactly. You could add a Like expression to each of the SQL statements like this:
vSQL = "SELECT * FROM myTable where city LIKE '*" & Text1 & "*';"
Noting the code above: If the City is say London then the * goes inside the single quote on both sides and the statement converts to:
... city LIKE '*London*';
If you set a break you can evalueate the vSQL
OOPS. I jus tnoticed I left out the = sign in the code above. Should be:
"SELECT * FROM myTable where city='" & Text1 & "';"
Regards,
Bill
If your form is named Form1 then what is the recordsource? If it's a table then the code will work but the user would need to type in the City exactly. You could add a Like expression to each of the SQL statements like this:
vSQL = "SELECT * FROM myTable where city LIKE '*" & Text1 & "*';"
Noting the code above: If the City is say London then the * goes inside the single quote on both sides and the statement converts to:
... city LIKE '*London*';
If you set a break you can evalueate the vSQL
OOPS. I jus tnoticed I left out the = sign in the code above. Should be:
"SELECT * FROM myTable where city='" & Text1 & "';"
Regards,
Bill
Palmtreeinfotech,
Just a note regarding your name field...
Using CHR(34)'s (double-quotes) as delimiters for your text fields instead of single quotes serves the purpose of preventing errors if you have any Irish names such as O'Brien (or other data that contains single quotes):
>> & chr(34) & me.YourTextboxName & chr(34)
Such data will cause problems if you delimit it with single quotes.
If you are using Wildcards in your SQL, you can use the CHR(34)'s like this:
"WHERE YourNameField LIKE " & chr(34) & me.YourTextbox & chr(34)
Just a note regarding your name field...
Using CHR(34)'s (double-quotes) as delimiters for your text fields instead of single quotes serves the purpose of preventing errors if you have any Irish names such as O'Brien (or other data that contains single quotes):
>> & chr(34) & me.YourTextboxName & chr(34)
Such data will cause problems if you delimit it with single quotes.
If you are using Wildcards in your SQL, you can use the CHR(34)'s like this:
"WHERE YourNameField LIKE " & chr(34) & me.YourTextbox & chr(34)
Sorry -
With wildcards, that should have been:
"WHERE YourNameField LIKE *" & chr(34) & me.YourTextbox & chr(34) & "*"
With wildcards, that should have been:
"WHERE YourNameField LIKE *" & chr(34) & me.YourTextbox & chr(34) & "*"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is totally awesome and thanks again. I will split the points up. Again, I appreciate all your answers!
Chris
Chris
ASKER
I learned something today. Thanks everyone.
Scott C