Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error (missing operator) (Access 2k)

    Hello All

    I'm trying to use an SQL string in VBA code, but I'm getting the error 3057 Syntax error (missing operator) due to the SQL I've entered. It's going to be something like my date format, but I've tried what little I know and it hasn't worked.

    Here's my SQL: -

    strWordSQL = "SELECT * FROM PrimaryDataTbl WHERE DateReceived BETWEEN # " & Me.StartDateTxt & " # AND # " & Me.EndDateTxt & " # VendorNumber = " & Me.VendorNumber

    I've tried it without the # marks around the dates. Added them in the hopes it was as simple as having to put the ' before any text items referred to in a SQL string.

    Using the de###### shows me that all three me.statements have the values in, using the SQL in the QBE window (with minor mods to suit the criteria) gives me the results I want......

    Please, help while I still have a little hair left that isn't either grey or on the desk <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Thanks

    Ian

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (missing operator) (Access 2k)

    Have you got a space after the first # and before the second # ? ie. # " and " #

    If so it will be looking for # 01/09/2004 # rather than #01/09/2004#

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (missing operator) (Access 2k)

    What if you use :
    strWordSQL = "SELECT * FROM PrimaryDataTbl WHERE DateReceived BETWEEN # " & Me.StartDateTxt & " # AND # " & Me.EndDateTxt & " # And VendorNumber = " & Me.VendorNumber
    Francois

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Syntax error (missing operator) (Access 2k)

    Ian,

    Is VendorNumber = " & Me.VendorNumber part of your strWordSQL statement? Also, Does the strWordSQL statement wrap as shown in your post or is it on one line?

    If wrapping the strSql string, then you would need something like this

    strWordSql = "Select * From" _
    & " SomeTable;"


    You will also need to add an ; at the end of your statement, for example,
    strWordSql = "Select * from SomeTable;"
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (missing operator) (Access 2k)

    Francois

    As yet I don't what's different between your SQL string and mine, but whatever it is it works <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> another learning opportunity provided by the good folks of Woody's lounge [img]/forums/images/smilies/biggrin.gif[/img]

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks

    Ian

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (missing operator) (Access 2k)

    You forget the And before VendorNumber.
    Between two conditions you have an AND or an OR.
    Gald it work for you now.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •