Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Syntax Help (2000)

    Hi,
    I have the following code:
    TheSelectSQL = "SELECT lngPartsID " & _
    "FROM qryReceiveQuotePartsRich " & _
    "WHERE lngRecSourceID= " & Me.lngRecSourceID & _
    "AND lngMachineID= " & Me.lngMachineID & _
    "AND curUnitPrice Is Not Null & _
    "AND ysnCCOrder= 0"
    the ysnCCOrder field is a yes/no field, I want to say if ysnCCOrder equals yes, but it keeps kicking back syntax error. Thanks,
    Deb

  2. #2
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    Hi Deb.

    If it's a yes/no field, then you need to say:

    ysnCCOrder= true, rather than =0


    Cheers

    Jim

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    I make that change and I still get a syntax message. I'm probably missing a " somewhere.
    Thanks,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Syntax Help (2000)

    The easiest way to debug a code-built SQL statement is to put a breakpoint in your code so that you stop at that line. After stepping through the line, you can print the value of your SQL string to the immediate window and look at it to see if you're missing any necessary spaces, punctuation, etc. If it looks OK, copy the SQL from the immediate window and paste it into the SQL view of a new query. Then you can see what specific message the query engine gives you and adjust it accordingly.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    I changed the code to below and now I get the following error:

    Syntax error (missing operator) in query expression 'lngRecSourceID=4AND lngMachineID=21AND curUnitPrice Is Not NullAND ysnCCOrder=true'.

    TheSelectSQL = "SELECT lngPartsID " & _
    "FROM qryReceiveQuotePartsRich " & _
    "WHERE lngRecSourceID= " & Me.lngRecSourceID & _
    "AND lngMachineID= " & Me.lngMachineID & _
    "AND curUnitPrice Is Not Null " & _
    "AND ysnCCOrder= true "
    Thanks,
    Deb

  6. #6
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    I build another one, just to test... compiled alright...

    strsql = "SELECT tblTest.autTestID, tblTest.strTestName, tblTest.intTestNum, tblTest.booTestyn " & _
    "FROM tblTest " & _
    "WHERE tblTest.strTestName = 'fred' AND tblTest.booTestyn=True"

    So did you mean you get a syntax error in the editor,or when you run the sql? If it compiles in the editor alright, then put a msgbox in the next line, like this:

    msgbox strsql

    to see what the result looks like.

    Hope that helps more.

    Jim

  7. #7
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    Ah... there is no space after the '4' in that one.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    I get that syntax when I run the code not compile. It compiles ok. the 4 is the result of that statement, it's not in the actual code.

    Any other thoughts?
    Deb

  9. #9
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    Okay.

    The resulting SQL string still needs to have those, or the SQL engine won't understand. You fix it by putting spaces before the 'AND' and 'WHERE' clauses, inside the quotes.

    That better?

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    I don't understand what you mean by "still needs those"? Do you mean the 4? I don't want that in the code because that's a hard number and I want that number to be determined by what is on the form.

  11. #11
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    What I mean is that your statement constructs a string that needs to be a valid SQL statement. It's not valid without the required spaces.

    If you change your statement so that it looks like this:

    TheSelectSQL = "SELECT lngPartsID " & _
    "FROM qryReceiveQuotePartsRich " & _
    "WHERE lngRecSourceID= " & Me.lngRecSourceID & _
    " AND lngMachineID= " & Me.lngMachineID & _
    " AND curUnitPrice Is Not Null & _
    " AND ysnCCOrder= true"

    That means the resulting statement will look like this:

    lngRecSourceID=4 AND lngMachineID=21AND curUnitPrice Is Not Null AND ysnCCOrder=true

    Try that!

    Jim

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    Thanks, I still need a quote after Null, but everything else worked. thank you very much!!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Lounger
    Join Date
    May 2001
    Location
    Wellington
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Syntax Help (2000)

    Hey, no problem. Pleased I could help.

    Cheers

    Jim

Posting Permissions

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