Extracting Values in a Field from SQL

[Larry R Harrison Jr]

I have Access 2000. I have the need to extract the range
of values from a specific field from an SQL expression.

The SQL expression is stored in a variable named gSQL so
it's always available once it's been generated.

The field whose values need extracting is autoid.

Basically, this gSQL variable has stored the results of a
search performed by the user. And if it has values of,
say, 3, 4, 10, 13 then it needs not only be extract to
extract those 4 values it would also need to be able to
pull up 3 and 13 as being the min/max, and it needs to be
able to store them in variables like, say, gMinID and
gMaxID.

Tips?

LRH

 

[Michel Walsh]

Hi,


I assume you mean a VBA variable. I also assume it is a string (not an array of integers). You
cannot refer to a VBA-variable from within Jet, but instead, you can use a VBA-function that will
return it:

Public Function MyFunction( ) as String
MyFunction=gSQL
End Function


Then, if you want a compiled query, you can try:

.... WHERE ( "," & MyFunction( ) & "," )
LIKE "*[ ,]" & FieldName & "[, ]*"


which will act like

... WHERE FIeldName IN( list of values in your string )



If you can compose yourself the text of the query, then, you may indeed use:

"... WHERE FieldName IN( " & gSQL & & ")"

and then, execute that string.


To get the min and the max, use DMin and DMax over the saved query, or build a query that will get
the MIN and the MAX you look for.



Hoping it may help,
Vanderghast, Access MVP