Results 1 to 8 of 8
  1. #1
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33

    line break in long query

    hi dudes can any body help me



    DoCmd.RunSQL "Update MarksDistribution
    SET MarksDistribution.urduT = Forms!marksDistribution!urdu,
    marksDistribution.englishT = Forms!marksDistribution!english
    WHERE class = Forms!marksDistribution!CList;"

    i want to break lines in query like above
    so it is easy to read

    Regards

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I tend to use a string variable strSQL and then set it line by line?
    Code:
    strSQL = " SELECT .... "
    strSQL = strSQL & " WHERE ....."
    strSQL = strSQL & " ORDER BY ...."
    Debug.Print strSQL
    The Debug is particularly valuable to see if it is as you *think* it is. Especially useful when concatenating variables/controls into the statement
    Too many people chuck a sql statement together and then wonder why it does not work
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Try https://www.dpriver.com/pp/sqlformat.htm and set the output to VB
    Code:
    varname1 = ""
    varname1 = varname1 & "Update MarksDistribution " & vbCrLf
    varname1 = varname1 & "SET MarksDistribution.urduT = Forms!marksDistribution!urdu, " & vbCrLf
    varname1 = varname1 & "marksDistribution.englishT = Forms!marksDistribution!english " & vbCrLf
    varname1 = varname1 & "WHERE class = Forms!marksDistribution!CList;"
    
    docmd.runsql varname1

  4. #4
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33
    Quote Originally Posted by Welshgasman View Post
    I tend to use a string variable strSQL and then set it line by line?
    Code:
    strSQL = " SELECT .... "
    strSQL = strSQL & " WHERE ....."
    strSQL = strSQL & " ORDER BY ...."
    Debug.Print strSQL
    The Debug is particularly valuable to see if it is as you *think* it is. Especially useful when concatenating variables/controls into the statement
    Too many people chuck a sql statement together and then wonder why it does not work

    Thanks its work fine form me

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Welshgasman View Post
    I tend to use a string variable strSQL and then set it line by line?
    Have been doing that for years, so it gets my vote. However, I started out by putting the space at the end of the line rather than the front but switched to the front. I can't recall if I ever missed a space, but if it needed trouble shooting it was a bit harder to determine if that was an issue or not. Just thought I'd mention about putting space at the beginning of the line for printsol's consideration.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    The space at the beginning is to save me scrolling to the end to check a space exists. OK, not needed on the very first line, so might be a typo.

    Also for printsol's consideration :-)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The space at the beginning is to save me scrolling to the end to check a space exists.
    Exactly my point. You just said it better!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not needed on the very first line, so might be a typo
    but you can still include one anyway - you do need at least one. the query interpreter will ignore additional spaces, and linefeeds.

    You also don't need the end semi colon, only required t the end of the parameter line if parameters are declared.

    I tend to use the linebreak characters, but still assign to a sqlstr before running/executing

    Code:
    dim sqlStr as string
    sqlStr="Update MarksDistribution" & _
               " SET MarksDistribution.urduT = Forms!marksDistribution!urdu," & _
               " marksDistribution.englishT = Forms!marksDistribution!english" & _
               " WHERE class = Forms!marksDistribution!CList;"
    docmd.runsql sqlStr
    however there is a limit of 10? linebreaks but easily handled by combining with the variable method already suggested

    An alternative view for slightly easier checking

    Code:
    sqlStr="Update MarksDistribution SET" & _
               " MarksDistribution.urduT = Forms!marksDistribution!urdu," & _
               " marksDistribution.englishT = Forms!marksDistribution!english" & _
               " WHERE class = Forms!marksDistribution!CList;"
    And you can drop quite a bit more if this code is in your marksDistribution form - this assumes that urduT and englishT are both text and clist is numeric

    Code:
    sqlStr="Update MarksDistribution SET" & _
               " MarksDistribution.urduT = '" & urdu & "'," & _
               " marksDistribution.englishT = '" & english "'" & _
               " WHERE class = " & CList
    and finally you can drop the table name if the field name is unique

    Code:
    sqlStr="Update MarksDistribution SET" & _
               " urduT = '" & urdu & "'," & _
               " englishT = '" & english "'" & _
               " WHERE class = " & CList
    saves a lot of typing

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

Similar Threads

  1. Line Break In My Forms List Box
    By BakerMan in forum Forms
    Replies: 6
    Last Post: 05-20-2019, 07:27 AM
  2. Query with line break divided lines of data?
    By securitywyrm in forum Queries
    Replies: 5
    Last Post: 06-06-2018, 12:48 PM
  3. Line Break in Textbox
    By alyon in forum Access
    Replies: 8
    Last Post: 02-16-2015, 10:21 AM
  4. Unbound Text Box with line break
    By DCV0204 in forum Forms
    Replies: 3
    Last Post: 01-24-2014, 09:32 AM
  5. macro message box - line break
    By tweety in forum Access
    Replies: 3
    Last Post: 04-05-2013, 05:17 PM

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