Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265

    Filter property (missing operator syntax error)

    I am testing the Filter property of a form. I am getting a syntax error saying that I am missing an operator.




    Code:
    Private Sub cmdFilter_Click()
        Me.Filter = "Last Name = C*"
        Me.FilterOn = True
    End Sub
    I am using the Northwind 2007 database, Customer Details form.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is "Last Name" a field name? If so, because of the space in the name, you need to use brackets around the field name => [Last Name]
    (You should never use spaces in object names) See http://access.mvps.org/access/tencommandments.htm

    Also, you need single quotes around C*. Do you have a last name of "C*"??

    Maybe try:
    Code:
    Me.Filter = "[Last Name] Like  'C*'"
    I *think* this should work..
    Last edited by ssanfu; 01-21-2014 at 07:26 PM. Reason: added site

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by ssanfu View Post
    Maybe try:
    Code:
    Me.Filter = "[Last Name] Like  'C*'"
    I *think* this should work..

    This worked. I don't know why but I did have to include single quotes. Do you know why that is?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Because [Last name] is text, you have to use a text string in the Like 'C*'. Text strings are delimited with quotes.
    Dates are delimited with #; no delimiter needed for numeric values.

  7. #7
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by orange View Post
    Because [Last name] is text, you have to use a text string in the Like 'C*'. Text strings are delimited with quotes.
    Dates are delimited with #; no delimiter needed for numeric values.
    Good information. Thank you. Can you provide any links that talk about these delimiters and when to use them?

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Looks like a very god website. I'll have to study it some more. Thank you.

  10. #10
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Ok. I am trying to use filter in another way. I have a form called 'gymnast' which has a field called 'gymnastID.' I have another form called 'frmGymnastFilter' which also has a field called 'gymnastID.'

    On the first form, I want to click a button, then onClick, open the 2nd form and filter it by the value in the gymnastID field of the first form. What am I doing wrong?


    Private Sub cmdOpenGym_Click()
    DoCmd.OpenForm "frmGymnastFilter", acNormal
    Forms!frmGymnastFilter.Filter = "Forms!frmGymnastFilter.gymnastID LIKE 'Me.gymnastID'"
    Forms!frmGymnastFilter.FilterOn = True
    End Sub

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Like" doesn't do anything when used with a number; it is only useful with text.
    You would have to concatenate the value, right now you are saying shoul me records where "gymnastID" is like Me.gymnastID.
    The correct syntax would be
    Forms!frmGymnastFilter.Filter = "Forms!frmGymnastFilter.gymnastID = " & Me.gymnastID


    It is easier to use the filter argument or the where argument of the "DoCmd.OpenForm" command.
    The syntax is

    DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)


    Your procedure could be
    Code:
    Private Sub cmdOpenGym_Click()
        DoCmd.OpenForm "frmGymnastFilter", , "gymnastID = " & Me.gymnastID
    End Sub
    This is provided "Me.gymnastID" is a number....

  12. #12
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by ssanfu View Post
    "Like" doesn't do anything when used with a number; it is only useful with text.
    Here is my code:

    Private Sub cmdOpenGym_Click()
    DoCmd.OpenForm "frmGymnastFilter", acNormal
    Forms!frmGymnastFilter.Filter = Forms!frmGymnastFilter.gymnastID Like "'" & Me.gymnastID & "'"
    Forms!frmGymnastFilter.FilterOn = True
    End Sub

    I forgot to mention that the field gymnastID is a text field because it is alphanumeric. Because of this, I thought it was necessary to enclose this in single quotes. But this is not working. How would I handle this?

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try:

    Forms!frmGymnastFilter.Filter = "GymnastID Like '*""" & Me.gymnastID & "*'"""

  14. #14
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by orange View Post
    Try:

    Forms!frmGymnastFilter.Filter = "GymnastID Like '*""" & Me.gymnastID & "*'"""
    I tried your suggestion above. I am getting a run-time error '3075': Syntax error in string in query expression 'GymnastID Like '*"33G*'"

    33G is one of the gymnastID numbers. Not sure what is wrong? Could it be that the " before the 33 is not necessary? Looks like it might be an extra ".

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this --we're trying to remove the double quotes in the rendered/final string. You want this in the rendered string 'GymnastID Like '*33G*'


    Forms!frmGymnastFilter.Filter = "GymnastID Like '*"" & Me.gymnastID & "*'""

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  2. Replies: 9
    Last Post: 01-22-2013, 04:23 PM
  3. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  4. Syntax error missing operator(3075)
    By parisienne in forum Programming
    Replies: 1
    Last Post: 04-07-2011, 02:29 PM
  5. Syntax Error...missing operator
    By jgelpi16 in forum Programming
    Replies: 14
    Last Post: 09-09-2010, 11:35 AM

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