Wildcard code

  • Thread starter Thread starter Sandy H
  • Start date Start date
S

Sandy H

Hi
I am trying to use the following code to filter a query but not having much
luck. Can anyone tell me what I'm doing wrong.

Function getJRegion() As String

strJRegion = Forms!frmReportsMenu!txtRegion
If strJRegion = "(ALL)" Then
getJRegion = "'*'"
Else
getJRegion = strJRegion
End If

End Function

I call getJRegion from a query. If the user wants to query one Region then
there will be a filter but if they want to query all regions there will be
no filter.

Thanks in advance
Sandy
 
Hi
I am trying to use the following code to filter a query but not having much
luck. Can anyone tell me what I'm doing wrong.

Function getJRegion() As String

strJRegion = Forms!frmReportsMenu!txtRegion
If strJRegion = "(ALL)" Then
getJRegion = "'*'"
Else
getJRegion = strJRegion
End If

End Function

I call getJRegion from a query. If the user wants to query one Region then
there will be a filter but if they want to query all regions there will be
no filter.

Thanks in advance
Sandy
Click to expand...

To use the "*" wildcard you must use the Like keyword, not =.

If strJRegion = "(ALL)" Then
getJRegion Like "'*'"
Else
getJRegion = strJRegion
End If
 
Thanks for the info Fred but the function I have detailed simply returns a
value that will be used in the query. It needs to return a value of some
sort. I have tried a few different ways of doing it like:

getJRegion = "Like '*'" and
getJRegion = ""

but none of these work. Any other ideas.

Sandy
 
Sandy,

How are you calling the function? Is it possible that you don't quote the
return? Or that you do quote the return and end up with "'*'"?

What results do you get?
--
Chaim


Sandy H said:
Thanks for the info Fred but the function I have detailed simply returns a
value that will be used in the query. It needs to return a value of some
sort. I have tried a few different ways of doing it like:

getJRegion = "Like '*'" and
getJRegion = ""

but none of these work. Any other ideas.

Sandy
Click to expand...
 
I am calling the function from the query criteria with getJRegion().


Chaim said:
Sandy,

How are you calling the function? Is it possible that you don't quote the
return? Or that you do quote the return and end up with "'*'"?

What results do you get?
Click to expand...
 
Sandy,

Not what I meant. Are you calling getJRegion() in such a way that the result
string ends up inside another set of quotes? If strJRegion = "(ALL)" for
example, the return is '*' not just *. Are you calling this in such a way
that you end up with "'*'" in your select query vs just '*' ? I.e., result
SQL looks like:

select <field list> from XYZ where region like "'*'"

instead of
select <field list> from XYZ where region like '*'
 
Back
Top