Results 1 to 4 of 4
  1. #1
    LightKnight is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7

    Filter a Recordset

    I am stuck with syntax on a query for a recordset. The code is:
    Set dbs = CurrentDb


    Set rst = dbs.OpenRecordset("tblContracts", dbOpenDynaset)


    'Filter the recordset
    rst.Filter = "[Status] = ' & vblStatus & '[LegalOwner] = ' & vblUser & '[MMIDivision] = ' & vblDivision & '"

    Set rstFiltered = rst.OpenRecordset



    The rst.Filter gives a syntax error. Is there a place that explains the syntax for a query like this?

  2. #2
    LightKnight is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    I think I got this part sorted. It now says: rst.Filter = "[Status] = '" & vblStatus & "'AND [LegalOwner] = '" & vblUser & "'AND [MMIDivision] = '" & vblDivision & "'"
    Now I get a too few parameters result but that is a different issue.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would open the original recordset on SQL that included the criteria, it would be more efficient. This will help make sure the string is correct:

    http://baldyweb.com/ImmediateWindow.htm

    The parameters error is Access telling you it cant find something, so double check the spelling of whatever is listed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you are missing a space before each AND

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

Similar Threads

  1. Filter Recordset by record count
    By jcc285 in forum Programming
    Replies: 14
    Last Post: 12-05-2017, 09:40 AM
  2. Can't set filter of an updateble recordset
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 02-17-2016, 09:25 PM
  3. Replies: 4
    Last Post: 05-20-2014, 12:45 PM
  4. Replies: 2
    Last Post: 10-25-2012, 02:53 AM
  5. Filter recordset
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 01-26-2011, 10:45 AM

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