How to use a function to specify contents for "In" criteria?

[Todd McDermid]

I'm wondering if this is possible...

I have a dynamic, short "list" of values that I would like to use as a
criteria in a standard Access Query. These values only apply to one column,
and I want to filter records that match any one of them. Coming from a T-SQL
background, the "In" criteria seems like the best place to start.

However, when I place "In (GetValues())" as the criteria for a column in the
query, it only works if the function "GetValues" returns a String, and that
string consists of ONE value to filter by:

* GetValues() -> "X" filters records with "X" in the column
* GetValues() -> "'X', 'Y'" does not find records that have either "X" or
"Y" in the column
* The query refuses to execute if GetValues is defined to return a Variant
(array)
* The query refuses to execute if the criteria is defined as "In GetValues()"

Does anyone know if the above type of thing is possible using "In" or
another construct in Access' query "language"?

TYVM

 

[Stefan Hoffmann]

hi Todd,

* The query refuses to execute if the criteria is defined as "In GetValues()"
Does anyone know if the above type of thing is possible using "In" or
another construct in Access' query "language"?

You can either rewrite your function, e.g.

Public Function CompareValues(AValue As Variant) As Boolean

'loop thru your list and compare with value
End Function

and use WHERE CompareValues([Field]) as condition.

The basic question: where do you get your list from?

mfG
--> stefan <--

 

[Ben]

Todd,

I don't know if this helps, but my experience is using IN clause, is:

WHERE xyz IN ("value1", "value2", "value3",...etc)

in your vba code, you probably would need to put extra quotes before and
after each value to have it read correctly, ie, """value1"", ""value2"",
""value3"",...." for it to read correctly.

HTH,

Ben

 

[Marshall Barton]

I'm wondering if this is possible...

I have a dynamic, short "list" of values that I would like to use as a
criteria in a standard Access Query. These values only apply to one column,
and I want to filter records that match any one of them. Coming from a T-SQL
background, the "In" criteria seems like the best place to start.

However, when I place "In (GetValues())" as the criteria for a column in the
query, it only works if the function "GetValues" returns a String, and that
string consists of ONE value to filter by:

* GetValues() -> "X" filters records with "X" in the column
* GetValues() -> "'X', 'Y'" does not find records that have either "X" or
"Y" in the column
* The query refuses to execute if GetValues is defined to return a Variant
(array)
* The query refuses to execute if the criteria is defined as "In GetValues()"

Does anyone know if the above type of thing is possible using "In" or
another construct in Access' query "language"?

It is not possible using IN. If your function returned a
string with the values, then you could use the same
technique described in the subquery thread from lesle
earlier today.

OTOH, Stefan described what could very well be a better way
to deal with an array of values.

 

[tina]

well, i think you can make it work if you change the query's SQL statement
at runtime, from VBA. i did a test in an A2000 db running in A2003. i
created a simple query called Query14, based on a table with two fields, as

SELECT * FROM tblData;

then i ran a couple procedures to change the SQL and then run the query, as

Public Sub isChangeSQL()

Dim str As String

str = "SELECT * FROM tblData WHERE City In (" & isGetValues & ")"
CurrentDb.QueryDefs("Query14").SQL = str
DoCmd.OpenQuery "Query14"

End Sub

Public Function isGetValues() As String

isGetValues = "'a','c'"

End Function

hth

 

[tina]

if I needed to return cities containing 'BA' (e.g. Baltimore) or

'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

Have you tested that? I may be misunderstanding (please correct me if I'm
wrong) but if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

Please clarify.

Bob

well, i think you can make it work if you change the query's SQL statement
at runtime, from VBA. i did a test in an A2000 db running in A2003. i
created a simple query called Query14, based on a table with two fields, as

SELECT * FROM tblData;

then i ran a couple procedures to change the SQL and then run the query, as

Public Sub isChangeSQL()

Dim str As String

str = "SELECT * FROM tblData WHERE City In (" & isGetValues & ")"
CurrentDb.QueryDefs("Query14").SQL = str
DoCmd.OpenQuery "Query14"

End Sub

Public Function isGetValues() As String

isGetValues = "'a','c'"

End Function

hth

I'm wondering if this is possible...

[quoted text clipped - 18 lines]

TYVM

 

[Michel Walsh]

SELECT whatever FROM somewhere WHERE " BA OM IJ " LIKE "* " & city & " *"


Important note: there is a space before the first group of character (BA)
and after the last one (IJ) you seek. It also assume you don't seek for
groups of characters with space in them (since the space is used as
delimiter for the element in the list, here). If you use a coma as
delimiter, that makes the statement:


SELECT whatever FROM somewhere WHERE ",BA,OM,IJ," LIKE "*," & city & ",*"


and then, it is important to NOT have a space after the coma, in the list of
codes you seek, ie, it is NOT: ",BA, OM, IJ," where the extra spaces are
thus considered as part or the sequence of characters you would search for.



Vanderghast, Access MVP

Hi Tina --

Thanks for that.

Could you provide us a practical example of where your code would work.

Best Wishes - Bob

if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

[quoted text clipped - 37 lines]

TYVM

 

[tina]

i already did, in my first post in this thread. as i said then, i did a test
in a real database, using a real table, with real values in it, and posted
the specific code that i tested. all the op needs to do is make any
modifications in *his* real GetValues() function so that it returns the
values in the proper syntax that my example shows. and if he decides to try
it, and has any questions, i'm sure he'll post back.

hth

Hi Tina --

Thanks for that.

Could you provide us a practical example of where your code would work.

Best Wishes - Bob

if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

[quoted text clipped - 37 lines]

TYVM

 

[Michel Walsh]

Well, what if you miss the S in SELECT... It is a computer that you are
using after all.

If you want to be friendly with your end user, then, nothing forbid YOU to
make your program checking the end user input, and add the missing space,
isn't it?


Vanderghast, Access MVP

And if I miss a space? Give me a break!

Bob

SELECT whatever FROM somewhere WHERE " BA OM IJ " LIKE "* " & city & " *"

Important note: there is a space before the first group of character (BA)
and after the last one (IJ) you seek. It also assume you don't seek for
groups of characters with space in them (since the space is used as
delimiter for the element in the list, here). If you use a coma as
delimiter, that makes the statement:

SELECT whatever FROM somewhere WHERE ",BA,OM,IJ," LIKE "*," & city & ",*"

and then, it is important to NOT have a space after the coma, in the list
of
codes you seek, ie, it is NOT: ",BA, OM, IJ," where the extra spaces are
thus considered as part or the sequence of characters you would search
for.

Vanderghast, Access MVP

Hi Tina --

[quoted text clipped - 18 lines]

TYVM

 

[John W. Vinson]

On Mon, 16 Feb 2009 11:21:52 -0500, "Michel Walsh"

Michel, I think one of us is confused. If the user has "Baltimore" in the
field [city], this criterion will fail, because " BA OM IJ " doesn't contain
the string "Baltimore", right?

SELECT whatever FROM somewhere WHERE " BA OM IJ " LIKE "* " & city & " *"


Important note: there is a space before the first group of character (BA)
and after the last one (IJ) you seek. It also assume you don't seek for
groups of characters with space in them (since the space is used as
delimiter for the element in the list, here). If you use a coma as
delimiter, that makes the statement:


SELECT whatever FROM somewhere WHERE ",BA,OM,IJ," LIKE "*," & city & ",*"


and then, it is important to NOT have a space after the coma, in the list of
codes you seek, ie, it is NOT: ",BA, OM, IJ," where the extra spaces are
thus considered as part or the sequence of characters you would search for.



Vanderghast, Access MVP

Hi Tina --

Thanks for that.

Could you provide us a practical example of where your code would work.

Best Wishes - Bob

if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

[quoted text clipped - 37 lines]

TYVM

 

[Michel Walsh]

You are right, indeed, I was confusing with equivalence with multiple
parameters in an IN list, but with full name supplied for the parameters,
not partial name as it is the case here.



Vanderghast, Access MVP

On Mon, 16 Feb 2009 11:21:52 -0500, "Michel Walsh"

Michel, I think one of us is confused. If the user has "Baltimore" in the
field [city], this criterion will fail, because " BA OM IJ " doesn't
contain
the string "Baltimore", right?

SELECT whatever FROM somewhere WHERE " BA OM IJ " LIKE "* " & city & " *"


Important note: there is a space before the first group of character (BA)
and after the last one (IJ) you seek. It also assume you don't seek for
groups of characters with space in them (since the space is used as
delimiter for the element in the list, here). If you use a coma as
delimiter, that makes the statement:


SELECT whatever FROM somewhere WHERE ",BA,OM,IJ," LIKE "*," & city & ",*"


and then, it is important to NOT have a space after the coma, in the list
of
codes you seek, ie, it is NOT: ",BA, OM, IJ," where the extra spaces are
thus considered as part or the sequence of characters you would search
for.



Vanderghast, Access MVP

Hi Tina --

Thanks for that.

Could you provide us a practical example of where your code would work.

Best Wishes - Bob

tina wrote:
if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it
wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

[quoted text clipped - 37 lines]

TYVM

 

[Michel Walsh]

A solution could then be:

SELECT *
FROM somewhere
WHERE EXISTS ( SELECT * FROM patterns WHERE city LIKE pattern )


where table patterns has one field, pattern, with one record per pattern:

Patterns 'table
Pattern 'fields
-------
*ba*
*om*
*ij* ' data


Sure, if the patterns are fix, can use:

SELECT *
FROM somewhere
WHERE city LIKE "*ba*"
OR city LIKE "*om*"
OR city LIKE "*ij*"



Vanderghast, Access MVP

You are right, indeed, I was confusing with equivalence with multiple
parameters in an IN list, but with full name supplied for the parameters,
not partial name as it is the case here.



Vanderghast, Access MVP

On Mon, 16 Feb 2009 11:21:52 -0500, "Michel Walsh"

Michel, I think one of us is confused. If the user has "Baltimore" in the
field [city], this criterion will fail, because " BA OM IJ " doesn't
contain
the string "Baltimore", right?

SELECT whatever FROM somewhere WHERE " BA OM IJ " LIKE "* " & city & " *"


Important note: there is a space before the first group of character (BA)
and after the last one (IJ) you seek. It also assume you don't seek for
groups of characters with space in them (since the space is used as
delimiter for the element in the list, here). If you use a coma as
delimiter, that makes the statement:


SELECT whatever FROM somewhere WHERE ",BA,OM,IJ," LIKE "*," & city & ",*"


and then, it is important to NOT have a space after the coma, in the list
of
codes you seek, ie, it is NOT: ",BA, OM, IJ," where the extra spaces
are
thus considered as part or the sequence of characters you would search
for.



Vanderghast, Access MVP



Hi Tina --

Thanks for that.

Could you provide us a practical example of where your code would work.

Best Wishes - Bob

tina wrote:
if I needed to return cities containing 'BA' (e.g. Baltimore) or
'OM' (e.g. Tacoma) or 'IJ' (e.g. Tijuana), that code wouldn't cut it.

yes, i did test the code, exactly as posted. and no, obviously it
wouldn't
work as posted if you needed to use the Like operator instead of the In
operator; you'd have to change the syntax of the WHERE clause.

hth

Hi Tina -

[quoted text clipped - 37 lines]

TYVM