Results 1 to 2 of 2
  1. #1
    Colin_W is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    1

    Syntax Error when Filtering on a Datasheet

    Scenario:


    The record source of Datasheet uses "SELECT * FROM .... " rather than explicitly specifying the required fields.
    The form design adds controls for a subset of fields.
    Some of the field names contain spaces.

    Problem:
    A syntax error occurs if the user attempts to do in-form filtering on any of the columns relating to fields containing spaces.

    Solution:
    In design mode, open the property sheet for the affected controls. Select the data tab and put square brackets [] round the Control Source.

    Comment:
    Yes - field names with spaces is bad practice. However, the form design is tolerant to it but fails if you want to use filtering.
    Access has been around for a long time yet there are still wrinkles like this.
    It is suggested that the design code automatically detects spaces and adds the brackets. This needs to be implemented when using the form wizard or the property sheet.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if field name = [My Field] , the text boxes will add underscore:

    me.filter = "[field]=" & me.My_Field
    me.filterOn = true

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

Similar Threads

  1. Text Filtering from a Datasheet Column
    By emhill57 in forum Access
    Replies: 1
    Last Post: 08-23-2017, 10:32 AM
  2. VBA form filtering syntax
    By N1755L in forum Forms
    Replies: 11
    Last Post: 05-09-2014, 06:10 PM
  3. Replies: 10
    Last Post: 12-05-2012, 04:02 PM
  4. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 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