Results 1 to 6 of 6
  1. #1
    ChaosInACT is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    3

    User functions within queries...

    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...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You don't show the wildcard in the string construction, how do they get in there?

    Why use the Like operator, you have exact values from the recordset. Try <> instead.

    Presume this criteria is all under one field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ChaosInACT is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    3
    Quote Originally Posted by June7 View Post
    You don't show the wildcard in the string construction, how do they get in there?

    Why use the Like operator, you have exact values from the recordset. Try <> instead.

    Presume this criteria is all under one field?
    Then names come from a list of specific employees we need to ignore, i put the stars in the table itself...

    I'll try <> when i get back to work first think in the morning, though it seems odd that typing the function return straight in there would work :/

    and yeah, only one field...

    Thanks for the idea, I'll let you know how it goes

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You have the asterisk in the data? I certainly don't understand your data but that doesn't sound like a good idea to me. I would never do that with a dataset. I don't allow wildcard characters or quotes in data (apostrophes only where I have given permission). And my suggestion won't work with the * in the data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ChaosInACT is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    3
    Quote Originally Posted by June7 View Post
    You have the asterisk in the data? I certainly don't understand your data but that doesn't sound like a good idea to me. I would never do that with a dataset. I don't allow wildcard characters or quotes in data (apostrophes only where I have given permission). And my suggestion won't work with the * in the data.
    it's a simple exclusion list of subcontractors... the data is coming from MYOB imports. basically the data entered in MYOB is...inconsistent so wildcards are sort of necessary. I can add them with vba but seemed to work so I left it...

    edit: sorry, by "seemed to work" I mean the debug.print result seemed to work, not the function in there :/

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, but wildcard characters don't belong in data, they belong in code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-22-2014, 08:29 AM
  2. Custom User Group queries
    By EliOklesh in forum Security
    Replies: 2
    Last Post: 10-29-2011, 01:12 PM
  3. Functions
    By Alex Motilal in forum Programming
    Replies: 2
    Last Post: 09-27-2010, 08:06 AM
  4. sum functions
    By trippers in forum Queries
    Replies: 2
    Last Post: 08-04-2010, 07:09 PM
  5. Replies: 0
    Last Post: 06-07-2007, 02:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums