04-17-09, 14:21 #1Registered User
- Join Date
- Apr 2009
- Hyattsville, MD
Unanswered: Search Multiple Fields with One Form Control
I have a query by form built in Access that searches a table of archaeological site data and returns the "hits" which match all of the cirteria set forth by the user who enters the form. The form and query work OK, but there is one problem. Archaeological sites are often known by more than one name. There is an official name established by the state, but not all users may know what that official name is. In our database we have two text fields (SITENAME and ALTNAME). SITENAME is the official name, while ALTNAME may list several alternative names that the site has been known by over time. Is there a way to have the query check the text entered by the user into a single text box control on the form for matches (or near matches) in both the SITENAME and ALTNAME fields of the data table.
I tried setting up two query columns (one for SITENAME and an identical one for ALTNAME) with the following expression:
Like "*" & [Forms]![QBF_SYNTH_FORM]![WhatName] & "*" Or [Forms]![QBF_SYNTH_FORM]![WhatName] Is Null
"QBF_SYNTH_FORM" is the Form that the query is called from and "WhatName" is the control (a text box) on the form that the data needs to match.
I get no results when I run the query if both of these columns are part of the query, but as long as I am only checking one field or the other it runs fine.
Thanks in Advance
Last edited by MacNachtan; 04-17-09 at 14:53.
04-17-09, 20:08 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
You could have 2 combobox (unbound) search fields on your form (ie. one called FSiteName and the other called FAltName.) Then in your query, under the SiteName column have your like statement (ie. Like "*" & [Forms]![QBF_SYNTH_FORM]![FSiteName] & "*") and under the AltName column have criteria (Like "*" & [Forms]![QBF_SYNTH_FORM]![FAltName] & "*")
If you're trying to do it based on a single search field, you can possibly use an expression in the query to combine the 2 field values and search upon that but a mass amount of records would slow the search down and I think it's easier to just base it upon 2 search fields.
Last edited by pkstormy; 04-17-09 at 20:12.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)