Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vba syntax for Query (2003 (11.6566.6568( SP2)

    I did a search and didn't find anything on point ... probably just a poor searcher. I'm attempting to build an SQL statement in vba and to run it with the docmd. The query is relatively simple, except I want to append a string to the beginning of a notes field and append the original notes field to it. The string that I'm attempting to append contains a carriage return/line feed at the end of it. I've successfully built and run query in the query design window. However when I copy the SQL to my vba code I'm unable to get the syntax for the notes field concatenation correct. The query sntax that woks is as follows:

    UPDATE C_tblProgramParticipants SET C_tblProgramParticipants.PPCompletedProg = True, C_tblProgramParticipants.PPartNotes = 'This is the Text to Append to the start of the Notes field' & Chr(13) & Chr(10) & C_tblProgramParticipants!PPartNotes
    WHERE (((C_tblProgramParticipants.ProgID)=1) AND ((C_tblProgramParticipants.PPCompletedProg)=False) AND ((C_tblProgramParticipants.PPPostedParticipant)=Fa lse))
    WITH OWNERACCESS OPTION;

    In my code below I've previously set stMsg to the text string that I would like to append. The third line is the one with which I'm having the problem .

    stSQL = "UPDATE C_tblProgramParticipants "
    stSQL = stSQL & "SET C_tblProgramParticipants.PPCompletedProg = True, "
    stSQL = stSQL & "C_tblProgramParticipants.PPartNotes = '" & stMsg & "' & '" & [C_tblProgramParticipants].[PPartNotes] & "' "
    stSQL = stSQL & "WHERE (((C_tblProgramParticipants.ProgID)=" & Me!lctlProgID & ") "
    stSQL = stSQL & "And ((C_tblProgramParticipants.ppPostedParticipant) = False) "
    stSQL = stSQL & "And ((C_tblProgramParticipants.PPCompletedProg) = False))"
    stSQL = stSQL & "WITH OWNERACCESS OPTION;"

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: vba syntax for Query (2003 (11.6566.6568( SP2)

    Three things I can suggest :

    * You need to be sure that the final sql will have spaces where they are needed. So you need a space at the end of each line, or at the start of the next.
    e.g. change
    stSQL = stSQL & "And ((C_tblProgramParticipants.PPCompletedProg) = False))"
    to
    stSQL = stSQL & " And ((C_tblProgramParticipants.PPCompletedProg) = False))"


    * Put in a msgbox stSQL or debug.print stSQL after you have compiled the SQL but before you execute it so you can look at the final text. This can help debug it.

    * Try this for line 3 (assuming you also want the Chr(13) & Chr(10) you nhad in the original query.
    stSQL = stSQL & " C_tblProgramParticipants.PPartNotes = '" & stMsg & "' " & Chr(13) & Chr(10) & "C_tblProgramParticipants.PPartNotes "
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba syntax for Query (2003 (11.6566.6568( SP2)

    John Hutchison's suggestion to insert a MsgBox or Debug.Print statement is excellent.
    In his suggestion for the third line, the Chr(13) & Chr(10) is outside the quotes, so it will insert a line break in stSQL instead of in the SQL statement you're trying to create (this is confusing).
    Try this - I simplified the SQL a bit.:
    <code>
    stSQL = "UPDATE C_tblProgramParticipants "
    stSQL = stSQL & "SET PPCompletedProg = True, "
    stSQL = stSQL & "PPartNotes = '" & stMsg & "' & Chr(13) & Chr(10) & PPartNotes "
    stSQL = stSQL & "WHERE ProgID = " & Me!lctlProgID & " "
    stSQL = stSQL & "And ppPostedParticipant = False "
    stSQL = stSQL & "And PPCompletedProg = False "
    stSQL = stSQL & "WITH OWNERACCESS OPTION"
    </code>
    or
    <code>
    stSQL = "UPDATE C_tblProgramParticipants "
    stSQL = stSQL & "SET PPCompletedProg = True, "
    stSQL = stSQL & "PPartNotes = " & Chr(34) & stMsg & Chr(34) & " & Chr(13) & Chr(10) & PPartNotes "
    stSQL = stSQL & "WHERE ProgID = " & Me!lctlProgID & " "
    stSQL = stSQL & "And ppPostedParticipant = False "
    stSQL = stSQL & "And PPCompletedProg = False "
    stSQL = stSQL & "WITH OWNERACCESS OPTION"
    </code>
    The latter version uses Chr(34), which stands for the double quote <code>"</code>. It looks a bit awkward, but I find it less confusing than combining single, which can be easy to overlook. But that's a matter of personal taste.

  4. #4
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba syntax for Query (2003 (11.6566.6568( SP2)

    Hans & John,

    Thanks for your input. Seeing the correct syntax, it makes perfect sense. Some how I just couldn't see it on my own ... even using the debug statement to look at the generated SQL.

    Appreciate your assistance,
    Marty

Posting Permissions

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