Using 'Like' more than once in a query

[ptaylor-west]

This is a problem that I thought I had resolved previously.

I am trying to use 'Like' more than once in the same query so that it allows users to define the query using multiple selections.

First selection is a Post Code using the criteria:

Like [Enter Post Code Like NE20 or blank for all] & "*"

Second selection is Business Type using the criteria:

Like [Enter Business Type or blank for all] & "*"

and finally Salesman using the citeria:

Like [Enter Salesmans Name or leave blank for all] & "*"

When the query is run it comes up with a box for each 'Like' so that you can enter your selection and then produces the results, but it produces incorrect results (ususally a blank screen). Any ideas?

I need to have this in a query so that I can e-mail it as an update

 

[Mile-O]

You would be best to allow the user to select these on a form and have the query reference the choices.

 

[ptaylor-west]

How? I thought you could only build a query from a Table or another query. What you suggest is a good idea.

 

[Mile-O]

Basically, you have the query built ensuring that your OR and AND statements are logically created.

Then, the criteria in these OR and AND situations would be based on the form:

i.e.

Like "*" & [Forms]![frmYourForm]![cboYourCombo] & "*"

 

[ptaylor-west]

I have tried what you suggest but I get the same results, the query doesn't seem to like mutilpe 'Like' choices. The problem probably lies in what you suggest in using logical AND/OR statements. It's as if the process needs to be slowed down allowing the query to process the first request before handling subsequent requests. Am I on the right track?

 

[Rich_Lovina]

Just 2 cents worth more. I use multiple queries a lot and find that using OR LIKE repeatedly on the one line yields correct result. Problem with the new line approach is that Access limited to eight lines on the Query Builder.

 

[ptaylor-west]

Definitely AND is clearly established, which is what I want. I have tried with combination of 10 records and the results are perfect but as soon as I apply the criteria to a much larger database, say 2,000 records plus that is when it doesn't produce any (or incorrect) results.

This indicates my previous fears that the query doesn't have enough time to respond.

Does this help?

I can always attach or forward the query for anyone to loook at and comment.

 

[Rich_Lovina]

My queries usually deal with 50,000 recs with no problem

 

[ptaylor-west]

I have attached the query, the fields are corrupted of course because of the lack of data but it shows the 'Like' staements that I am using.

Does this cast any light on my problem?

 

[Jon K]

Since you haven't attached any test data, it's difficult to see exactly what the problem is. My guess is there are some null values in the fields Post Code, Nature of Business, Status, and Salesman. These records cannot be returned by your query.


To use "blank for all" in the parameters, you can set each of the four criteria in a column like this in the query grid:-

Field: IIf(IsNull([Enter Post Code Like NE20 or blank for all]), True, [Prototype].[Post Code] Like [Enter Post Code Like NE20 or blank for all] & "*")

Show: uncheck
Criteria: <>False


The word True in the expression will return every record including those with null values.

 

[ptaylor-west]

Spot on,

What a releif!!!!!

What is annoying is the amount of times that Null fields catch me out, I haven't learnt in 2years!

Grateful thanks.