Ok, I SWEAR I have done this many times before, I even found working examples. The only difference I can see is I'm working with strings, not integers. anyway...
I'm trying to dynamically filter a query by putting a return function in it's criteria. I've done this before returning integer values without a problem, but no joy here. now for the details...
the code:
Public Function BuildSubExceptions() As String
Dim rstSubcontractorsExclusions As DAO.Recordset
Set rstSubcontractorsExclusions = CurrentDb.OpenRecordset("tblSubcontractorsExclusio ns")
Dim strFilterString As String
strFilterString = "Not Like """ & rstSubcontractorsExclusions!MYOBName & """"
rstSubcontractorsExclusions.MoveNext
Do Until rstSubcontractorsExclusions.EOF
strFilterString = strFilterString & " And Not Like """ & rstSubcontractorsExclusions!MYOBName & """"
rstSubcontractorsExclusions.MoveNext
Loop
Debug.Print strFilterString
BuildSubExceptions = strFilterString
rstSubcontractorsExclusions.Close
Set rstSubcontractorsExclusions = Nothing
End Function
and here is what the function IS returning according to debug.print
Not Like "Scott O'Regan*" And Not Like "Peter Connolly*" And Not Like "Frank Sakoua*" And Not Like "Hooper, Jason*" And Not Like "Advanced Plumbing & Drains Pty Ltd*" And Not Like "Evergreen Hotwater & Plumbing Specialists*" And Not Like "Peter Connelly*"
now, I can copy and paste the debug.print results into query and it works, but the function returns an empty query, which is wrong. I've tried changing the " to ' and no joy. I'm stumped, I've used this technique heaps and the return IS working...
am I mad??? any advice???
Thanks in advance!
edit: i've tried wrapping it in an eval function, no joy there either...