Multi-value Search Query

[thesven]

So I have been through all the postings I can read here but unable to address my issue:

I have 4 unbound text boxes on a form to produce a filtered search on these four fields. The problem has been narrowed down to one of these fields "Client Contact" which is a simple text field identical to two of the other fields. When I add the criteria for this field the results returned and no data. If I remove the criteria from this field and only use the other three I get the proper results.

Here is the SQL I see when the three fields are used and the proper results are shown:

WHERE (((TimeSheets.[Week Ending]) Like "*" & [Forms]![TimeSheet Search]![Week Ending] & "*") AND ((TimeSheets.[Employee Name]) Like "*" & [Forms]![TimeSheet Search]![Employee Name] & "*") AND ((TimeSheets.Client) Like "*" & [Forms]![TimeSheet Search]![Client] & "*"))
ORDER BY TimeSheets.[Week Ending];

I need to be able to search the fourth text field (similar to the others) but adding the same criteria yields no results or wrong results.

Here is the SQL when I enter the criteria in the fourth: (which doesn't display what I'm after)

WHERE (((TimeSheets.[Week Ending]) Like "*" & [Forms]![TimeSheet Search]![Week Ending] & "*") AND ((TimeSheets.[Employee Name]) Like "*" & [Forms]![TimeSheet Search]![Employee Name] & "*") AND ((TimeSheets.Client) Like "*" & [Forms]![TimeSheet Search]![Client] & "*") AND ((TimeSheets.[Client Contact]) Like "*" & [Forms]![TimeSheet Search]![Client Contact] & "*"))
ORDER BY TimeSheets.[Week Ending];

My goal here is to enter any search criteria and in any unbound text box and have it return those fields that match or a combination of search criteria entered in several unbound text boxes in my search form.

Any help is appreciated.

 

[zooropa66]

I'd first check that the textbox values you are using in your query are as expected.

Comment out your Query and insert the following:

Dim myvar1, myvar2, myvar3, myvar4 As String

myvar1 = [Forms]![TimeSheet Search]![Week Ending]
myvar2 = [Forms]![TimeSheet Search]![Employee Name]
myvar3 = [Forms]![TimeSheet Search]![Client]
myvar4 = [Forms]![TimeSheet Search]![Client Contact]


MsgBox "Value 1 = " & myvar1
MsgBox "Value 2 = " & myvar2
MsgBox "Value 3 = " & myvar3
MsgBox "Value 4 = " & myvar4

If the values are as you expect then the next thing i'd do would be to build the query string using the variables myvar1, myvar2, myvar3 and myvar4 then output this to MsgBox - this will let you see the actual query String rather than a string that has the names of your form controls on it

 

[thesven]

Thanks for the info zooropa66.

The textbox values all appear to work as they should. The issue arises when trying to search using the [Forms]![TimeSheet Search]![Client Contact]

Without this the other three function as they should with this one removed. Query runs, reports build all is good. I was assuming using the same criteria method would allow me to show a result set based on the the [Forms]![TimeSheet Search]![Client Contact] unbound textbox if a search was performed.

I actually found a a video tutorial outling the exact process I followed here published by Alansidman

datapigtechnologies dot com/flashfiles/searchform.html

(I can't post the real named link here as I have to have 10 posts on the forum to do this, so my help is further limited by this support board)


I'm obviously overlooking something. I'm wondering if it is related to this field having null values along with Client Contacts. I thought about trying to implement a listbox that a user could simply select the Client Contact on the search form and display those records but I'm not bright empugh to figure this out yet.

 

[zooropa66]

Why not test your Nulls theory out by replacing Nulls with some dummy text

 

[thesven]

Well, some progress....adding the following criteria to the Query under Client Contact does allow me to input a text search with good results HOWEVER if any of the other search fields are populated it returns zippo.

Like [Forms]![TimeSheet Search]![Client Contact] & "*"

 

[thesven]

If I move the Like [Forms]![TimeSheet Search]![Client Contact] & "*" to be an OR criteria the other fields work as they should but the Client Contact data set is always returned with those records that contain data in this field even thought the unbound text box is blank when the search is done.

I'm so close I can taste it......

 

[thesven]

Here is the SQL as referenced in my last post

WHERE (((TimeSheets.[Week Ending]) Like Forms![TimeSheet Search]![Week Ending] & "*") And ((TimeSheets.[Employee Name]) Like Forms![TimeSheet Search]!Employee_Name & "*") And ((TimeSheets.Client) Like Forms![TimeSheet Search]!Client & "*")) Or (((TimeSheets.[Client Contact]) Like Forms![TimeSheet Search]![Client Contact] & "*"))
ORDER BY TimeSheets.[Week Ending];

 

[zooropa66]

Could you could post a stripped down version of your database with just the forms,controls and tables relevant to this problem? You can attach an mdb file up to 2M in size i believe

 

[thesven]

I can appreciate and understand the request however this is a live database housing a multitude of confidential information as you might imagine. I am currently just trying to build a search form against the database.

I am willing and able to try any possible solutions to my problem and produce the results.

 

[zooropa66]

I took a look at another post. How about starting with this

WHERE
TimeSheets.[Week Ending]
Like “*” & Forms![TimeSheet Search]![Week Ending] & "*" And
TimeSheets.[Employee Name]
Like “*” & Forms![TimeSheet Search]!Employee_Name & "*" And
TimeSheets.Client
Like “*” & Forms![TimeSheet Search]!Client & "*" And
TimeSheets.[Client Contact]
Like “*” & Forms![TimeSheet Search]![Client Contact] & "*"
ORDER BY TimeSheets.[Week Ending];

I suspect that your query shouldn't have a mixture of AND & OR
Also, i don't think you need all those parentheses ()
Also check that you haven't missed any undescore characters between the words that make up your field names as i notice that you have a mixture of some with and some without an underscore character.

and obviously the above is all just on the one line - i just showed it over several lines so you can see the repeating structure

 

[vbaInet]

My goal here is to enter any search criteria and in any unbound text box and have it return those fields that match or a combination of search criteria entered in several unbound text boxes in my search form.

What do you mean by this statement? Are the unbound textboxes the controls where the data should be displayed after searching?

 

[thesven]

I tried the process with the same results. I rebuilt several new queries to try and figure this out, all yielding the same results. If I place Like "*" & [Forms]![SearchForm]![qclientcontact] & "*" in the Client Contact criteria section of the query I can search and retrieve this filed IF all other fields are left blank. If I enter data into any other search field along with this field the query returns zero results.

Here is the final version of the SQL as shown by Access 2010 for the criteria section:

HAVING (((TimeSheets.[Week Ending]) Like "*" & [Forms]![SearchForm]![qweek] & "*") AND ((TimeSheets.[Employee Name]) Like "*" & [Forms]![SearchForm]![qemployee] & "*") AND ((TimeSheets.Client) Like "*" & [Forms]![SearchForm]![qclientname] & "*") AND ((TimeSheets.[Client Contact]) Like "*" & [Forms]![SearchForm]![qclientcontact] & "*") AND (([TimeSheets]![Approval Status])="Approved"));

 

[thesven]

no, these are the search boxes on the form, upon clicking the "run query" button the results are derived.

 

[vbaInet]

They are derived from where? Is your form bound?

 

[thesven]

The form runs a query. Not sure I understand your question.

 

[zooropa66]

Here's what i guessed you had - 4 unbound textboxes (so when you are looking at your form in design mode the textboxes will say "unbound" in them)

into each of these textboxes, you enter some characters to be used as part of your wildcard query. This query is then used to retrieve one or more records from a single field in a table that meet the search criteria.

Before you run your query did you set all your text box values to an empty string

i.e.
mytxt1 = ""
mytxt2 = ""
mytxt3 = ""
mytxt4 = ""

to avoid a Null (empty) value messing things up?

 

[vbaInet]

By bound I mean, what is the Record Source property of your form?

What is the Control Source property of the controls that should display the results?