Per request:Just copied the entire function TO THE END OF THIS POST. Adding "Public" did not help, saw no "missing" listed in References. OTHER FUNCTIONS IN THE COPIED MODULE ARE ACTING THE SAME WAY. PERHAPS IT HAS SOMETHING TO DO WITH COPYING MODULE TO DIFFERENT DATABASE.Orig question follows:
Access 2000 function works fine when working from the immediate window, but when I try to use it in a report, I get error msg 2427.
From the immediate window, swtchnm("Nor, SAM") works as designed. when used in a report control's data source, i.e. =swtchnm([FieldName]) I get error msg "2427 You entered an expression that has no value".
The function works fine in reports in the database in which I originally created it. I just copied the module from one db to the one I'm having the problem with. I don't understand this. If I use just [FieldName] as the control's data source the raw value of the field displays. At the error dialog box, Debug points to third line of the SwtchNm function:
Public Function SwtchNm(strtdesc) As String
Dim strgdnm As Variant
strgdnm = InStr(1, strtdesc, ",")
If strgdnm <> 0 Then
SwtchNm = mixed_case((Right(strtdesc, Len(strtdesc) - strgdnm - 1))) & " " & mixed_case(Left(strtdesc, strgdnm - 1))
SwtchNm = mixed_case(strtdesc)