Wildcard code

[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

 

[fredg]

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

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

If strJRegion = "(ALL)" Then
getJRegion Like "'*'"
Else
getJRegion = strJRegion
End If

 

[Sandy H]

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

 

[Guest]

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

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 H]

I am calling the function from the query criteria with getJRegion().

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?

 

[Guest]

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 '*'