use VBA function as criteria

[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!

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

THANK YOU! I KNEW there was something simple I was missing.

THANKS KEN!