Results 1 to 11 of 11
  1. #1
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Syntax error in Keyword Search code

    Hello,

    I am trying to create a button that will filter data in a subform to match what is written in a text box. I am using the following code:
    "
    Private Sub cmdSearchPrice_Click()


    Dim SQL As String

    SQL = "SELECT [Price].[ID], [Price].[CodeName], [Price].[PricingType], [Price].[PricingDate], [Price].[Price] " _
    & "FROM Price " _
    & "WHERE [CodeName] LIKE '*" & Me.txtCodeName & "*' " _
    & "WHERE [PricingDate] LIKE '*" & Me.txtPriceDate & "*' " _
    & "ORDER BY [Price].[CodeName] ; "

    Me.subPrice.Form.RecordSource = SQL

    End Sub
    "
    The code works when I do not have the second "WHERE... line. When I add that line to refine the search to include date, it gives the following error:
    "
    Run-time error '3075':
    Syntax error (missing operator) in query expression '[CodeName] LIKE '**' WHERE [PricingDate] LIKE '**"
    "

    I am teaching myself VBA and am very new to this, so please and thanks for any help!

    -Brian

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try replacing the second "WHERE" with "AND" or "OR"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is how you learn!

    Do you know how to debug?

    Add a line after the SQL string:
    Debug.Print SQL
    (SQL may be reserved in Access, to be safe change it to "strSQL" or something like that)

    The SQL statement will show up in the Immediate window of the VBA editor. Copy it and paste it into a new query. Try and run the query, see if it works.

    Once you have the query working you will know what changes to make to the string here.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For details on SELECT query SQL

    Only 1 WHERE in the basic Select as Bob said.

    "OR" ===> any condition is true

    "AND" ===> all conditions must be true.

  5. #5
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Awesome! Thanks, I no longer get an error.

    However, now when I type anything into either text box, there are no values that are filtered/returned.

    For reference, this is what my code now looks like:

    Private Sub cmdSearchPrice_Click()
    Dim SQL As String

    SQL = "SELECT [Price].[ID], [Price].[CodeName], [Price].[PricingType], [Price].[PricingDate], [Price].[Price] " _
    & "FROM Price " _
    & "WHERE [CodeName] LIKE '*" & Me.txtCodeName & "*' AND [PricingDate] LIKE '*" & Me.txtPriceDate & "*' " _
    & "ORDER BY [Price].[CodeName] ; "

    Me.subPrice.Form.RecordSource = SQL

    End Sub

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Replace the AND with OR and try again.

    ??? Is PricingDate a Date/Time data type in the table?

  7. #7
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Yes, PricingDate is a Date/Time data type

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The Like operator only compares Strings...not Numbers or Dates...which makes sense if you think about it! How is one number 'like' another number? Or one date 'like' another date?

    You might try using the Conversion Function, CStr() against the DateTime Field to convert it to a String and see if Access will then use Like...but once again...how is one date 'like' another date?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Or how about:
    Code:
    Private Sub cmdSearchPrice_Click()
      Dim SQL As String
    
      SQL = "SELECT [Price].[ID], [Price].[CodeName], [Price].[PricingType], [Price].[PricingDate], [Price].[Price] " _
          & "FROM Price " _
          & "WHERE [CodeName] LIKE '*" & Me.txtCodeName & "*' AND [PricingDate] = #" & Me.txtPriceDate & "#" _
          & "ORDER BY [Price].[CodeName] ; "
    
      Me.subPrice.Form.RecordSource = SQL
    
    End Sub

  10. #10
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Steve,

    Thanks for your assistance! This is what my code now looks like:

    Private Sub cmdSearchPrice_Click()
    Dim SQL As String

    SQL = "SELECT [Price].[ID], [Price].[CodeName], [Price].[PricingType], [Price].[PricingDate], [Price].[Price] " _
    & "FROM Price " _
    & "WHERE [CodeName] LIKE '*" & Me.txtCodeName & "*'" _
    & "AND [PricingDate] = #" & Me.txtPriceDate & "# " _
    & "ORDER BY [Price].[CodeName] ; "

    Me.subPrice.Form.RecordSource = SQL

    End Sub


    However, I'm getting the syntax error again:

    Run-time error '3075':
    Syntax error in date in query expression '[CodeName] LIKE '**'AND [PricingDate] =#'


    Any idea what the issue could be?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would add a line "Debug.Print SQL" and set a breakpoint on the "Me.subPrice.Form.RecordSource = SQL" line.
    Look at the immediate window to see if the SQL is properly formed.

    Did you know that "SQL" is a reserved 'word' in Access and shouldn't be used for object names? (Maybe use "strSQL" or "sSQL")


    Looking at the error message, it looks like you are missing a space in front of the "AND".


    This is how I would create the string:
    Code:
    Private Sub cmdSearchPrice_Click()
      Dim sSQL As String
    
      sSQL = "SELECT [Price].[ID], [Price].[CodeName], [Price].[PricingType], [Price].[PricingDate], [Price].[Price]"
      sSQL = sSQL & " FROM Price" 
      sSQL = sSQL & " WHERE [CodeName] LIKE '*" & Me.txtCodeName & "*'"
      sSQL = sSQL & " AND [PricingDate] = #" & Me.txtPriceDate & "#"
      sSQL = sSQL & " ORDER BY [Price].[CodeName];"
    
      '  Debug.Print sSQL
    
      Me.subPrice.Form.RecordSource = SQL
    
    End Sub
    I add the separating space at the beginning of each part, with no space at the end.

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

Similar Threads

  1. Keyword Search from Subform
    By jdowning in forum Forms
    Replies: 5
    Last Post: 05-20-2016, 07:47 AM
  2. Replies: 4
    Last Post: 04-21-2015, 07:12 PM
  3. keyword search
    By Mbakker71 in forum Access
    Replies: 5
    Last Post: 02-05-2014, 06:03 AM
  4. Syntax Error in my Query Code
    By PPat in forum Queries
    Replies: 5
    Last Post: 04-19-2013, 03:12 PM
  5. Incorrect syntax near keyword “ORDER"
    By k9drh in forum Import/Export Data
    Replies: 1
    Last Post: 06-13-2011, 07:36 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