Using like operator in criteria of a query

[leobaby]

In the hopes of creating a usable search tool for my database, I created a query with five of the fields criteria set to use the like operator. Heres an example criteria: Like [Forms]![frmPowerSearch]![criteria1]
In the frmPowerSearch, I have text fields with default value set to "*". This way, running (or requerying) the query when the field is left default shows all of the records.

The problem is that it does not. It only shows a record when All of the fields that have a criteria are not null or blank.

Since this seams to spoil what would be a very cool search feature, Any suggestiongs would be much appreciated.

Thanks Much,
Matthew

 

[spencertaylor]

In Access query window, the criteria line uses OR: to indicate the available use of the OR relationship. If your criteria for each field is all on the same row in the query this is treated as an AND.

So put your criteria in separate rows for an OR and the same line for an AND.

 

[MangroBongacello]

Try this: In the criteria grid insert criteria like this

iif([Forms]![frmPowerSearch]![criteria1]="*";Like [Forms]![frmPowerSearch]![criteria1] Or Is Null;Like [Forms]![frmPowerSearch]![criteria1])

I didn't test it. It's just an idea.
Mangro

 

[leobaby]

Thanks for the replies. I found an interesting solution to this problem, if thats what you want to call it.

I have tried many variations on the immediate if to no avail whatsoever. I am pretty sure access does not understand the is null being inside of the iif and its possible it doesnt even understand the like. btw, iif uses commas not semicolons. Again my excitment was abated; iif would be a great solution to this problem. I was so close.

Next I tried adding another criteria to handle the isnull. So I had two criteria lines for five fields(criteria1 to 5):
Like [Forms]![frmPowerSearch]![criteria1]
Is Null And [Forms]![frmPowerSearch]![criteria1]="*"

The only apparent problem with this is that across the critia row, fields are anded together; so that all criteria must match the like or all criteria must be * and null.

So I replaced the two criteria for each of the five fields with this one line and saved and closed the query.
Like [Forms]![frmPowerSearch]![criteria1] Or (Is Null And [Forms]![frmPowerSearch]![criteria1]="*&quot

What I'm thinking access tried to do was create a row of criteria with each possibility. kinda like

aaaaa
baaaa
abaaa
bbaaa
etc

So is having 30 or 100 rows of criteria a terrible thing for access to deal with? Will this kill my application in the future?

Is there another way to write this criteria line so that I don't have to do this grid thing?

Is there something I am missing that is making using multiple like statements not work for me?

Thanks much,
Matthew

 

[leobaby]

Aparently this doesnt work either since access doesnt like the Is Null And [Forms]![frmPowerSearch]![criteria1]="*"


Any suggestions would be greatly appreciated.
Matthew

 

[AnalystDBA]

leobaby,

Try this.

Like [Forms]![frmPowerSearch]![criteria1] & "*"

Like does not like Null values. By adding the wildcard operator after the field reference it should work OK even if the criteria filed is null it just searches for anything using the Wildcard operator (*).
You should be able to use this in each of your criteria fields.

Cheers
AnalystDBA

 

[MangroBongacello]

AnalystDBA, there is one problem with your suggestion. For example, if you set the criteria to "Tom", the query would return "Tom", "Tomas", "Tommy", not just "Tom". The query should return all those results only if you set the criteria to "Tom*".

leobaby, try this:

First, make a backup copy of the query

Then delete all the criteria from the query (from now q1).

Then create new query (q2) for which the source (from) is q1.

Then put a button on your PowerSearch form.

Put the following code on click (of course, you will have to make some adjustments)

dim qry as querydef
set qry=currentdb.querydefs ("q2&quot
dim strSQL as string
dim strWhere as string

if criteria1 = "*" then
strWhere = "(q2.field1 like '" & criteria1 & "' or isnull(q2.field1))"
else
strWhere = "q2.field1 like '" & criteria1 & "'"
end if

if criteria2 = "*" then
strWhere = strWhere & " and (q2.field2 like '" & criteria2 & "' or isnull(q2.field2))"
else
strWhere = strWhere & " and q2.field2 like '" & criteria2 & "'"
end if

...and so on for all the criteria fields

strSQL = "select * from q2 where " & strWhere & ";"
qry.sql=strSQL

This should give you the results you need. Now I did test it and it worked. Good luck.

Mangro