UDF in query of linked table Causes #Error

[David Taylor]

The following query returns #Error when tblDocuments is a table in a linked
Access database.

SELECT PCase(tblDocuments.DocumentFilePathNew) FROM tblDocuments

The UDF is:
Public Function PCase(strIn) As String
Dim strArr() As String
Dim intCtr As Integer

strArr = Split(strIn, " ")
For intCtr = 0 To UBound(strArr)
strArr(intCtr) = UCase(left(strArr(intCtr), 1)) &
LCase(Mid(strArr(intCtr), 2))
Next intCtr
PCase = Join(strArr, " ")

End Function

Thanks

DT

 

[Jerry Whittle]

Does the function work when run in the database containing the table?

Are there any null records in the field?

 

[David Taylor]

Does the function work when run in the database containing the table?

Yes

Are there any null records in the field?

No



DT



Thanks for prompt response

 

[David Taylor]

This is truly strange. tblDocuments.DocumentFilePathNew contains data for
all the records in the table. BUT if I test for blank fields like

Public Function PCase(strIn) As String
Dim strArr() As String
Dim intCtr As Integer

If Nz(strIn, "") <> "" Then
strArr = Split(strIn, " ")
For intCtr = 0 To UBound(strArr)
strArr(intCtr) = UCase(left(strArr(intCtr), 1)) &
LCase(Mid(strArr(intCtr), 2))
Next intCtr
PCase = Join(strArr, " ")
Else
PCase = ""
End If

End Function

Then it works. Thanks for your assistance.

DT

 

[Jerry Whittle]

I'm assuming that you have compiled the problem database and all is well there.

Then next thing that I would check are references. In the VB window go up to
Tools, References and see if there are any problems such as a broken or
missing reference.

 

[David Taylor]

Please DISREGARD the former post saying that all was well.

No. No broken or missing references. In fact other UDFs in this database are
also returning #Error when run from a query.

DT