here's a whacky one: help on multiple wildcard criteria in query

[Noel]

Any thoughts would be appreciated.

The criteria I'm using in the query comes from a function: 'getJobGroup()'

Specifically, the criteria in the query reads: Like getJobGroup()

The function is:
Function getJobGroup()
Dim v
v = Forms![frm: cfax_main]!txtJobGroupCriteria
Select Case v
Case 1
getJobGroup = Chr(34) & Chr(42) & "0" & Chr(34) & Chr(32) & "Or Like" & Chr(32) & Chr(34) & Chr(42) & "9" & Chr(34)
Case 2
getJobGroup = "*1 or like *2"
Case 3
getJobGroup = "*P"
End Select
'MsgBox (getJobGroup)
End Function

Case 3 works fine, but the moment I try to add another wildcard (case 2 or case 1) to the string, the query returns 0 records.

I've also tried:
- spelling out the string using ascii in case 1
- not using the text "or" or "or Like" in between wildcards (i.e. "*1 *2")

Thanks in advance for providing any input.

The result is the same if I change the criteria to read directly from the form (i.e. "Like Forms![frm: cfax_main]!txtJobGroupCriteria") and put the branching logic in the form accordingly.

Noel

 

[PauloR]

Noel,

You should use " like " in each wildcard

ex: " Like *1 or Like *2 "

 

[Noel]

thanks, but

I think I tried every iteration with 'Like' and 'or'.

"Like *1 or Like *2" didn't work either??

Other thoughts?

 

[jal]

I think I tried every iteration with 'Like' and 'or'.

"Like *1 or Like *2" didn't work either??

Other thoughts?

It was my understanding that you have to wrap the keyword in quotes like this:

Like "*1"

and I believe single quotes work as well.

Like '*1'

But I doubt you are allowed to put two likes in a row.
WHERE Name Like string1 or Like string2 (probably not valid)

I would think you have to do this
Where Name Like string1 or Name Like string2


'

 

[georgedwilkinson]

Is the column you're comparing this to a number type or a text type?

You cannot use a wildcard with a number type without first converting it.

Also, you cannot say:
x like y or like z

It must read:
x like y or x like z

You need to re-think this.

 

[Noel]

thanks, but

jal/George - thanks for the input.

jal - thanks, but I've tried those too. I tried to duplicate what Access does for you if you type in multiple criteria directly into the query

E.g., if you enter criteria directly into the query (such as: *1 or *2), Access reformats the criteria to read: Like "*1" Or Like "*2"... and the query returns values as expected

George - thanks - I thought of that too. I'm comparing strings; so, I'm assuming you need either a single or double quote around the string comparison value(s)

I've tried passing the criteria to the query two ways: 1) via a textbox in a form:
Like [Forms]![frm: cfax_main]![txtJobGroupCriteria]
2) I also tried passing the criteria via a function so that the query criteria written is:
Like getJobGroup()

When I pass a single criterion, e.g. setting txtJobGroupCriteria (or getJobGroup) = *1, then the query returns what I expect (i.e. many rows where the field value ends in '1')

When I try to pass a multiple criteria string that looks just like what acces would reformat criteria strings to look like (witnessed via debug.print or msgbox), I get nothing.

 

[jal]

jal/George - thanks for the input.

jal - thanks, but I've tried those too. I tried to duplicate what Access does for you if you type in multiple criteria directly into the query

E.g., if you enter criteria directly into the query (such as: *1 or *2), Access reformats the criteria to read: Like "*1" Or Like "*2"... and the query returns values as expected

George - thanks - I thought of that too. I'm comparing strings; so, I'm assuming you need either a single or double quote around the string comparison value(s)

I've tried passing the criteria to the query two ways: 1) via a textbox in a form:
Like [Forms]![frm: cfax_main]![txtJobGroupCriteria]
2) I also tried passing the criteria via a function so that the query criteria written is:
Like getJobGroup()

When I pass a single criterion, e.g. setting txtJobGroupCriteria (or getJobGroup) = *1, then the query returns what I expect (i.e. many rows where the field value ends in '1')

When I try to pass a multiple criteria string that looks just like what acces would reformat criteria strings to look like (witnessed via debug.print or msgbox), I get nothing.

I'm sorry, Noel, but i find it hard to believe you are doing it just like we advised you without success. I just wrote my first Access application with a search query which, due to multiple search fields, had multiple LIKE clauses in a single query, and it worked fine. Please make sure you are not doing this:

Where Name Like "*1 or Name Like *2"

The problem with the above is only 1 set of quotes. It should read:

Where Name Like "*1" or Name Like "*2"

(two sets of quotes, because one set for each keyword).

 

[jal]

By the way, you may be able to use Control C to print the contents of a MsgBox. Perhaps you may wish to dump out some MsgBox info onto this thread so that we can see exactly what query is being executed.

 

[WayneRyan]

Noel,

When you use as criteria --> Like getJobGroup()

Access will interpret that VERY specifically.

It will try to match the field with the string returned by the function.

That means that even though it "looks" OK to have --> "Like '*1' Or Like '*2'"
Access will try to match that as ONE string value, not TWO comparisons!

For that same reason your function can't return --> "Like '*1' Or [Fieldx] = 'x'"
And obviously you can't go totally nuts and --> "Like '*1' Order By SomeField"

The criteria has a very limited scope in this sense.

My best suggestion would be for you to look at QueryDefs. That way you can present
the entire SQL to Access and it will make sense in that context.

That is very tough to explain,
Wayne

 

[Noel]

hmmm

jal (etal?)

It maybe you think I'm writting the criteria into a sql string (ala rs.openrecordset(sql)).

I'm actually using the Access query objects for this.

Using the OpenRecordset method will not work for me because I'm using the criteria in preliminary queries that I then join in a subsequent (make table) query.

It sounds like I may need to use the CreateQueryDef method instead.

I just thought someone knew the answer to the hickup in my approach.

 

[WayneRyan]

Noel,

I'm very aware that you are using the Query Grid. That's the source of the problem.

When you express ONE criteria in the grid, you can't change in "midstream" to TWO
comparisons.

I don't even think that a Parameter query would change this.

If you use the QueryDef, when you assign the SQL to it, Access will treat the
criteria properly.

Wayne

 

[Noel]

thanks

I followed Wayne's advise and changed my approach to using the CreateQueryDef method and encountered no glitches when passing multiple (wildcard keywords) criteria to the query.

Thanks all for the input and getting me to suck it up and claim failure with my original approach I think I lean toward using the given Access objects way too much.

 

[WayneRyan]

Noel,

Glad to hear that you have it working.

You're original approach didn't "fail".

In the Query grid ... what would have happened if your function had returned
a result like 'In (1,2,3,4)? The person that wrote that code assumed that
your function would return a SINGLE string to compare against the field.

If it didn't kill us, it just made us stronger ...

Wayne