Dlookup and Function 1

[dmon000]

Why does this work ok?
? DLookup("[LIST_NAME]", "[EMail_Dist_List]", "[RECEPIENTS]='" & fOSUserName & "'")

But when this same line is in a function, it does not work:
Public Function APGroup()
APGroup = DLookup("[LIST_NAME]", "[EMail_Dist_List]", "[RECEPIENTS]='" & fOSUserName & "'")
End Function

fosusername is a function that returns the network username and is my desired criteria for the dlookup function.

Thanks!!

 

[Remou]

In what way does it not work?

 

[dmon000]

In the immediate window the dlookup function works fine and returns the correct information.

The function when in the immediate window "? apgroup" returns nothing.

 

[JoeAtWork]

What happens if you change it to this:

Public Function APGroup() [COLOR=red]As String[/color]
APGroup = [COLOR=red]Nz([/color]DLookup("[LIST_NAME]", "[EMail_Dist_List]", "[RECEPIENTS]='" & fOSUserName & "'")[COLOR=red],"No Match")[/color]
End Function

 

[ck1999]

Is this fOSUserName declared globaly

Should you use

Public Function APGroup(fOSUserName)
APGroup = DLookup("[LIST_NAME]", "[EMail_Dist_List]", "[RECEPIENTS]='" & fOSUserName & "'")
End Function

ck1999

 

[Remou]

Did you put the function in the module belonging to a form? If so, you need to move it to an ordinary module.

 

[dmon000]

Thanks for all your help!

JoeAtWork's suggestion got a return of "no match", which is false. the dlookup code alone returns "test" which is correct.


CK1999's suggestion once again returns nothing, as does the original code.

As per Remou's suggestion, I placed the code in an ordinary module and it works!!
Thanks !!!!!