Gary,
Access 2003.
mdb
The function is utlitizing a table link to Sybase.
The Query is:
SELECT TransmittalDocument.APC, TransmittalDocument.DrawingNo,
FileFound([Unit],[Type],[SubType],[APC]) AS MFSFileFound,
TransmittalDocument.InSystem
FROM TransmittalDocument
WHERE (((TransmittalDocument.APC) Is Not Null) AND
((FileFound([Unit],[Type],[SubType],[APC]))=True) AND
((TransmittalDocument.InSystem) Is Not Null) AND ((TransmittalDocument.Unit)
Is Not Null) AND ((TransmittalDocument.Type) Is Not Null) AND
((TransmittalDocument.SubType) Is Not Null));
However, instead of TRUE I've also tried 0 and -1 (without quotes).
I tried returning a string (TRUE or FALSE), but I have the same problem.
The function does not take long to run. The FileFound function is defined as:
' Returns true if the file information provided resulted in a file found
' on the MFS (T: drive), otherwise False is returned
Public Function FileFound(Unit As String, docType As Byte, docSubType As
Byte, APC As String) As Boolean
Dim path As String
Dim typeName As String
Dim subTypeName As String
' Build path from the inputs
typeName = DLookup("DirName", "DocType", "TypeID=" & docType)
subTypeName = DLookup("lan_path", "dbo_lan_based_parm",
"apc_doc_type_code=" & _
docType & " And apc_doc_stype_code=" & docSubType)
path = "T:\mfs\U" & Unit & "\" & typeName & "\" & Trim(subTypeName) &
"\CURRENT"
FileFound = FileSrch(path, APC & ".") ' The "." is so that FileSrch
does not wildcard at the end of the APC number
End Function
The FileSrch function used in the above function looks like:
' Returns a True if the file exists in the specified directory, otherwise
' False is returned. File name must end with a ".", or it is equivalent to
' "filename*". File extension is not checked.
Public Function FileSrch(dirName As String, fName As String) As Boolean
Dim i As Integer
Dim path As String
On Error GoTo PROC_ERR
With Application.FileSearch
.NewSearch
.LookIn = dirName
.SearchSubFolders = False
.FileName = fName
' .MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
FileSrch = True
' MsgBox "There were " & .FoundFiles.count & _
' " file(s) found."
' For i = 1 To .FoundFiles.count
' MsgBox .FoundFiles(i)
' Next i
Else
FileSrch = False
' MsgBox "There were no files found."
End If
End With
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Function
Gary Walter said:
Leif said:
I've built a VBA function that returns a TRUE/FALSE value. I've defined
the
return data type of the function as boolean.
In a query I pass certain values from a table to the VBA function. It
works
just fine returning a result that is displayed as 0 or -1. However, if I
try
to place a value in the criteria portion of the query, such as TRUE, I get
a
message that it has an incompatible data type. If get the same message if
I
use either a 0 or -1 for criteria.
In addition, if I run the query without the criteria, and then right-click
on the field and select either Filter by Selection or Filter Excluding
Selection, I get the same error message. In other words, it appears that
for
a function return value in a query no possible filter is allowed.
PMFBI
something doesn't sound right
in Access 2000/2002 mdb,
a simple function that returns boolean
can be filtered on for -1 or 0...no problem.
what version of Access?
mdb or adp?
is the data in a mdb?
linked table?
what is the WHERE clause?
is Access wrap -1 or 0 in quotes
(which you don't want obviously)?
something like:
WHERE fSomething([SomeField]) = -1
what happens if you change function
to return string of either "TRUE" or "FALSE"?
is function inordinately complicated
(takes long time to run)?
