Undefined function 'fGetGroupName' in expression

[Harlan]

I have the following code

Public GroupName As String

------------------------------------------------------------------

Function fGetGroupName() As String

fGetYourGlobal = GroupName

End Function

-------------------------------------------------------------------

DIM ..... all the variables, SET db.... etc





strSQL = "SELECT dbo_PIP.* FROM dbo_PIP WHERE
(((dbo_PIP.problem_eval_user_group_cd)=fgetGroupName()));"



Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)



when i run this, I get "Undefined function 'fGetGroupName' in expression"
error



I have tried every combination of ' and " and ( and ) I can think of..... I
know there is something wrong with the strSQL string, where am I going
wrong?



Thanks

Harlan

 

[Marshall Barton]

I have the following code

Public GroupName As String

------------------------------------------------------------------

Function fGetGroupName() As String

fGetYourGlobal = GroupName

End Function

-------------------------------------------------------------------

DIM ..... all the variables, SET db.... etc





strSQL = "SELECT dbo_PIP.* FROM dbo_PIP WHERE
(((dbo_PIP.problem_eval_user_group_cd)=fgetGroupName()));"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

when i run this, I get "Undefined function 'fGetGroupName' in expression"
error

When you run a query from a VBA procedure using
OpenRecordset or Execute, it bypasses Access and goes
directly to the database engine (Jet, SQL, etc) which are
unaware of user defined functions.

The way to do what you want is to put the **value** of the
function in the SQL string (instead of a call to the
function). If the problem_eval_user_group_cd is a Text
field, use:

strSQL = "SELECT dbo_PIP.* FROM dbo_PIP WHERE
dbo_PIP.problem_eval_user_group_cd=""" & fgetGroupName() &
""""

If the field is a numeric type, use:

strSQL = "SELECT dbo_PIP.* FROM dbo_PIP WHERE
dbo_PIP.problem_eval_user_group_cd=" & fgetGroupName()

 

[Harlan]

Thanks Marsh...... that worked great..... that's why some are MVP's and some
aren't...