Results 1 to 5 of 5
  1. #1
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    Incomplete Syntax Clause (syntax error)


    I think you all probably know by now that I'm working on a project. At any rate though, have something I can't get my head around again:
    Code:
    Set rs = db.OpenRecordset("SELECT * FROM '" & pTblName & "' " & _
                              "WHERE ['" & pFldName & "'] = '" & pName & "' " & _
                              "ORDER BY '" & sDateFld & "'", dbOpenDynaset)
    can anyone spot the error? the immediate window shows single quotes around the concats, so if Access is reading it that way it's not the correct input. thanks!

  2. #2
    SteveH2508 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117
    Is sDateFld a date? If so your delimiters should be #

  3. #3
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by SteveH2508 View Post
    Is sDateFld a date? If so your delimiters should be #
    steve. I don't think they should, right? does this makes sense...?
    Code:
    select * from table
    
    ORDER BY #[order_date]#
    that's not right. is that what you're saying? a date field in an order by clause is not enclosed in # signs. that's only for a criteria clause, which I have none here that contain dates.

  4. #4
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    steve,

    this is the solution that access accepts:
    Code:
    Set rs = db.OpenRecordset("SELECT * FROM " & tbl & " " & _
                              "WHERE [" & pfld & "] = '" & pname & "' " & _
                              "ORDER BY [" & dtefld & "]", dbOpenDynaset)
    this also gives the correct sql printout in the immediate window. problem solved. but my question is now, why is this not following the correct concatenation conventions of string variables? it's reading it like I'm using number variables instead of string variables. is this right?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,712
    The only place you would use the delimiter is around the value for the criteria. You would never have delimiters around the table name or the field name in any clause. In other words, the only place you'd have a delimiter is around the value being looked for:

    WHERE FieldName = 'Paul'
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Syntax Error Help with Recordset
    By ajetrumpet in forum Programming
    Replies: 11
    Last Post: 09-08-2010, 02:39 PM
  2. Where clause syntax
    By stupesek in forum Reports
    Replies: 7
    Last Post: 09-03-2010, 02:26 PM
  3. Syntax error
    By smikkelsen in forum Access
    Replies: 6
    Last Post: 04-28-2010, 09:38 AM
  4. Syntax Error
    By KLynch0803 in forum Programming
    Replies: 11
    Last Post: 02-04-2010, 01:45 AM
  5. sql syntax error
    By SMAlvarez in forum Access
    Replies: 1
    Last Post: 03-12-2009, 09:43 PM

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 - Senior Forums