UDF in query of linked table Causes #Error

  • Thread starter Thread starter David Taylor
  • Start date Start date
D

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
 
Does the function work when run in the database containing the table?

Are there any null records in the field?
 
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
 
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.
 
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
 
Back
Top