Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


delete last carriage return

Posted on 2011-04-18
Medium Priority
Last Modified: 2012-05-11
at the end of the string below I get 2 or 3 carriage returns. How would I get rid of these?
Dim rs

Set qd = CurrentDb.QueryDefs("qryInvoice_1ToExportToAccountingInsert")
For cnt = 0 To qd.Parameters.Count - 1
   qd.Parameters(cnt) = Eval(qd.Parameters(cnt).Name)
Next cnt

Set rs = qd.OpenRecordset()

Do While Not rs.EOF

'Amount including tax
sSql = sSql & "411000," & rs!ClientCodeID & ",0," & rs!TotalIncludingTax & "," & rs!InvoiceDate & _
",VT," & rs!InvoiceCompanyName & "," & rs!InvoiceNumber & vbCrLf

'Amount excluding tax
sSql = sSql & "706100,," & rs!TotalExcludingTax & ",0," & rs!InvoiceDate & _
",VT," & rs!InvoiceCompanyName & "," & rs!InvoiceNumber & vbCrLf

If rs!TotalTax <> 0 Then
sSql = sSql & "445713,," & rs!TotalTax & ",0," & rs!InvoiceDate & _
",VT," & rs!InvoiceCompanyName & "," & rs!InvoiceNumber & vbCrLf
End If

'rs("DateExportedToAccounting") = date
        'go to next recotd
    Set rs = Nothing

sSql = Replace(sSql, vbCrLf & vbCrLf, vbCrLf)

Open in new window

Question by:Shawn
LVL 58

Accepted Solution

cyberkiwi earned 2000 total points
ID: 35420691
while Right(sSQL, 2) = vbCrLf
   sSQL = Left(sSQL, Len(sSQL) -2)

Author Comment

ID: 35420709
perfect. thank you

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question