use VBA function as criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The answer is probably simple but it is eluding me. I have a VBA function
called GetAcctMgr that returns either a person's name or all. (I'll talk
syntax later). This function is used as criteria for a query. The query has
some YTD and prior YTD calculations in it so it is using aggregates (sum).
The field where I am using my function is actually a combined field of the
employee's first/last name.

So, the function GetAcctMgr looks like this:
....
If intSup <> 0 Then
If intStation = 0 Then
' employee has supervisor but no assigned station --- NTR
GetAcctMgr = strName
Else
' employee has a supervisor and an assigned station --- SM
GetAcctMgr = "LIKE '*'"
End If
Else
' employee has no supervisor --- Sales Mgr's boss or station GM
GetAcctMgr = "LIKE '*'"
End If

The variable intSup is from a supervisor field and I am just checking to
see its value before determining whether I want the employee's name or all
names.

This query runs fine if I manually enter "Joe Smith" or if Like "*". When I
substitute this text with GetAcctMgr() I get nothing.

Is the answer obvious and I've looked at this too long to be able to see it?

Thanks in advance!
 
I don't see where you assign a value to strName. It is probably earlier in
your code. I would look to see that it is styled correctly so that it will
match the data in your table.
 
Put the LIKE operator in the query rather than as part of the function's
return value, e.g.

WHERE YourField LIKE GetAcctMgr(<arguments list>)

The function would need to be amended:

If intSup <> 0 Then
If intStation = 0 Then
' employee has supervisor but no assigned station --- NTR
GetAcctMgr = strName
Else
' employee has a supervisor and an assigned station --- SM
GetAcctMgr = "*"
End If
Else
' employee has no supervisor --- Sales Mgr's boss or station GM
GetAcctMgr = "*"
End If

Ken Sheridan
Stafford, England
 
Back
Top