Using a custom function in a query

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

Let's say I have a function in a global module called
GetNames (which returns something like 'ARM','CAT') and I
want to run a query like the following:

SELECT *
FROM TableA
WHERE Name IN (NAMELIST)

where NAMELIST is filled in with value returned by
GetNames function. Am I correct in assuming that the
syntax would be

SELECT *
FROM TableA
WHERE Name IN (GetNames())

For some reason this does not seem to work...just want to
make sure I'm going about the right way.
 
Hi Bob,

I have never found a way to "program"
a list that will work within IN. It just plain
wants a literal string.

I once used something like below:

Public Function fInList(Fld, strList As String) As Boolean
If InStr(strList, "'" & Fld & "'") > 0 Then
fInList = True
Else
fInList = False
End If

End Function

SELECT *
FROM TableA
WHERE fInList([Name],GetNames());

the advantage of function is that you
could add error checking and input
validation.

but you can see that you might just use:

SELECT *
FROM TableA
WHERE
InStr(GetNames(), "'" & [Name] & "'") > 0;

This may be "expensive" depending on
what is going on in GetNames().

If you have already saved the list string
in a global var, and GetNames() is just
a wrapper to return this global var, it
might help.

or it may be to your advantage in this case
to "combine" GetNames() with InStr() in
another function. You know your data best.

Good luck,

Gary Walter
 
Hi Bob,

I forgot to mention the most obvious
solution (if possible).

In some event, just write the SQL using
GetName().

For example, if query is used for recordsource
of a report, in report's open event:

Dim strSQL As String

strSQL = "SELECT * FROM TableA " _
& "WHERE Name IN (" & GetNames() & ")"
Me.Recordsource = strSQL

If you need to use as stored query,
then you can just redefine the SQL
of the query in the event where you
will need it using Duane's ChangeSQL
function.

Function ChangeSQL(pstrQueryName As String, pstrSQL As String)
On Error GoTo Err_ChangeSQL
Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
qd.SQL = pstrSQL

qd.Close
db.Close

Exit_ChangeSQL:
Set qd = Nothing
Set db = Nothing
Exit Function
Err_ChangeSQL:
MsgBox Err.Description
Resume Exit_ChangeSQL

End Function

So...say you have button to open
a form bound to this query, in button's
OnClick event

Dim strSQL As String

strSQL = "SELECT * FROM TableA " _
& "WHERE Name IN (" & GetNames() & ")"
ChangeSQL "yourqueryname", strSQL
DoCmd.OpenForm.....

I think you probably understand.

Good luck,

Gary Walter

Gary Walter said:
Hi Bob,

I have never found a way to "program"
a list that will work within IN. It just plain
wants a literal string.

I once used something like below:

Public Function fInList(Fld, strList As String) As Boolean
If InStr(strList, "'" & Fld & "'") > 0 Then
fInList = True
Else
fInList = False
End If

End Function

SELECT *
FROM TableA
WHERE fInList([Name],GetNames());

the advantage of function is that you
could add error checking and input
validation.

but you can see that you might just use:

SELECT *
FROM TableA
WHERE
InStr(GetNames(), "'" & [Name] & "'") > 0;

This may be "expensive" depending on
what is going on in GetNames().

If you have already saved the list string
in a global var, and GetNames() is just
a wrapper to return this global var, it
might help.

or it may be to your advantage in this case
to "combine" GetNames() with InStr() in
another function. You know your data best.

Good luck,

Gary Walter

Let's say I have a function in a global module called
GetNames (which returns something like 'ARM','CAT') and I
want to run a query like the following:

SELECT *
FROM TableA
WHERE Name IN (NAMELIST)

where NAMELIST is filled in with value returned by
GetNames function. Am I correct in assuming that the
syntax would be

SELECT *
FROM TableA
WHERE Name IN (GetNames())

For some reason this does not seem to work...just want to
make sure I'm going about the right way.
Click to expand...
Click to expand...
 
Thanks Gary:

This raises another question for me...is it possible to
directly reference global variables in queries? For
example,

SELECT *
FROM TableA
WHERE TheName = gsCurrentName

where gsCurrentName is a global variable. I will, of
course, try this and one of us can post the answer for
the benefit of others.

So, if I understand your last suggestion, if it made
sense to do so, I could, say, have a function called
IsAChosenName(ByVal sNameToFind) which returns True if
the name passed is contained in a global variable which
stores a comma separated list of selected names and False
otherwise...correct? I'm going to try that as I am
currently storing the list of selected names this way in
a global variable.
 
I wish that I could do this, but due to the fact that the
primary query involved references other queries and
tables (4 layers deep!), and since it is a couple of
layers deep that the need to use the function exists, it
would be problematic at best.

The ChangeSQL function would be perfect if it wasn't for
the fact that this is a multi-user application and it is
possible that multiple individuals could be running this
query at the same time with different names. Will keep
that in mind, though, for other applications. Thanks...
 
This raises another question for me...is it possible to
directly reference global variables in queries? For
example,

SELECT *
FROM TableA
WHERE TheName = gsCurrentName

where gsCurrentName is a global variable.
Click to expand...

The only way I know to use a global variable in a query
is to wrap it in a code module function.

Option Compare Database
Option Explicit
Public gsCurrentName As Variant

Public Function GetCurrentName() As Variant
GetCurrentName = gsCurrentName
End Function
Public Function SetCurrentName(pCurrentName As Variant)
gsCurrentName = pCurrentName
End Function

SELECT *
FROM TableA
WHERE TheName = GetCurrentName()
..
Gary Walter
 
Back
Top