cjmitton
06-21-2013, 04:30 AM
I'm using word 2010, on a custom form I'm find a client address from a MS SQL 2008 DB, its being added to a recordset where I'm using the result to send it to a text file (at current).
I have an issue where each record has trailing 'spaces' after the main information pulled from the address. to give you an example I'm using underscore as a replacement for 'spaces':
1_High_Street_______________________
Townname__________________________
BigCity_____________________________
Countysomewhere____________________
Postcode_______
I need to remove the trailing 'spaces' but of course leave the genuine spaces in place:
1_High_Street
Townname
BigCity
Countysomewhere
Postcode
I've googled this and tried to use various functions to remove the spaces from 'strings' of text (I accessed the recordset then got each field as a string then tried the function)
This option failed and was wondering if there was any other options.
Here's my code at the moment, I've not added the code for the function though, Also adjusted the DB names! Also I have 'two' outputs as i 'play' with the code one's a message box the other is a text file. Ideally I'd like to output to a text file but I'm 'playing' at the moment to find the best answer!
Private Sub getaddress()
Dim CltCode As Long
Dim i As Integer
Dim objMyConn As Object
Dim objMyCmd As Object
Dim objMyRecordset As Object
Dim strAddree As String
Dim strAddl1 As String
Dim strAddl2 As String
Dim strAddl3 As String
Dim strAddl4 As String
Dim strAddpc As String
Dim fFile As Long
Dim strFile As String
Dim ii As Integer
fFile = FreeFile
'Nominate the output text file path and name
strFile = "C:\TextFileName.Txt"
' Set the Variable with relevent code / data
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
CltCode = LstResults.Value
'Open Connection to the Database
objMyConn.ConnectionString = "Driver=SQL Server;Server=SQL\SQL;Database=db1;Trusted_Connection=Yes;"
objMyConn.Open
'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "SELECT fm_addree, fm_addli1, fm_addli2, fm_addli3, fm_addli4, fm_poscod FROM fmsaddr WHERE (fmsaddr.fm_addtyp='CL') and (fm_clinum Like '%" & CltCode & "')"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
objMyRecordset.MoveFirst
strAddree = objMyRecordset!fm_addree & ""
fRemoveExcessChars (strAddree)
strAddl1 = objMyRecordset!fm_addli1 & ""
fRemoveExcessChars (strAddl1)
strAddl2 = objMyRecordset!fm_addli2 & ""
fRemoveExcessChars (strAddl2)
strAddl3 = objMyRecordset!fm_addli3 & ""
fRemoveExcessChars (strAddl3)
strAddl4 = objMyRecordset!fm_addli4 & ""
fRemoveExcessChars (strAddl4)
strAddpc = objMyRecordset!fm_poscod & ""
fRemoveExcessChars (strAddpc)
If Not objMyRecordset.EOF And Not objMyRecordset.BOF Then
Open strFile For Output As #fFile
Do Until objMyRecordset.EOF
'Loop across all fields in recordset delimiting them with a tab key
For ii = 0 To objMyRecordset.Fields.Count - 1
strString = strString & objMyRecordset(ii) & vbNewLine
Next
'Output the resulting string to the text file
Print #fFile, strString
'Reset the string to empty for the next record
strString = ""
'Move to the next record in the recordset
objMyRecordset.MoveNext
Loop
'Close the recordset and the text file
' Rs.Close
Close #fFile
End If
objMyRecordset.Close
objMyConn.Close
Set objMyConn = Nothing
Set objMyCmd = Nothing
Set objMyRecordset = Nothing
MsgBox "Address: " & vbCrLf & strAddree & vbCrLf & strAddl1 & vbCrLf & strAddl2 & vbCrLf & strAddl3 & vbCrLf & strAddl4 & vbCrLf & strAddpc
End Sub
I have an issue where each record has trailing 'spaces' after the main information pulled from the address. to give you an example I'm using underscore as a replacement for 'spaces':
1_High_Street_______________________
Townname__________________________
BigCity_____________________________
Countysomewhere____________________
Postcode_______
I need to remove the trailing 'spaces' but of course leave the genuine spaces in place:
1_High_Street
Townname
BigCity
Countysomewhere
Postcode
I've googled this and tried to use various functions to remove the spaces from 'strings' of text (I accessed the recordset then got each field as a string then tried the function)
This option failed and was wondering if there was any other options.
Here's my code at the moment, I've not added the code for the function though, Also adjusted the DB names! Also I have 'two' outputs as i 'play' with the code one's a message box the other is a text file. Ideally I'd like to output to a text file but I'm 'playing' at the moment to find the best answer!
Private Sub getaddress()
Dim CltCode As Long
Dim i As Integer
Dim objMyConn As Object
Dim objMyCmd As Object
Dim objMyRecordset As Object
Dim strAddree As String
Dim strAddl1 As String
Dim strAddl2 As String
Dim strAddl3 As String
Dim strAddl4 As String
Dim strAddpc As String
Dim fFile As Long
Dim strFile As String
Dim ii As Integer
fFile = FreeFile
'Nominate the output text file path and name
strFile = "C:\TextFileName.Txt"
' Set the Variable with relevent code / data
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
CltCode = LstResults.Value
'Open Connection to the Database
objMyConn.ConnectionString = "Driver=SQL Server;Server=SQL\SQL;Database=db1;Trusted_Connection=Yes;"
objMyConn.Open
'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "SELECT fm_addree, fm_addli1, fm_addli2, fm_addli3, fm_addli4, fm_poscod FROM fmsaddr WHERE (fmsaddr.fm_addtyp='CL') and (fm_clinum Like '%" & CltCode & "')"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
objMyRecordset.MoveFirst
strAddree = objMyRecordset!fm_addree & ""
fRemoveExcessChars (strAddree)
strAddl1 = objMyRecordset!fm_addli1 & ""
fRemoveExcessChars (strAddl1)
strAddl2 = objMyRecordset!fm_addli2 & ""
fRemoveExcessChars (strAddl2)
strAddl3 = objMyRecordset!fm_addli3 & ""
fRemoveExcessChars (strAddl3)
strAddl4 = objMyRecordset!fm_addli4 & ""
fRemoveExcessChars (strAddl4)
strAddpc = objMyRecordset!fm_poscod & ""
fRemoveExcessChars (strAddpc)
If Not objMyRecordset.EOF And Not objMyRecordset.BOF Then
Open strFile For Output As #fFile
Do Until objMyRecordset.EOF
'Loop across all fields in recordset delimiting them with a tab key
For ii = 0 To objMyRecordset.Fields.Count - 1
strString = strString & objMyRecordset(ii) & vbNewLine
Next
'Output the resulting string to the text file
Print #fFile, strString
'Reset the string to empty for the next record
strString = ""
'Move to the next record in the recordset
objMyRecordset.MoveNext
Loop
'Close the recordset and the text file
' Rs.Close
Close #fFile
End If
objMyRecordset.Close
objMyConn.Close
Set objMyConn = Nothing
Set objMyCmd = Nothing
Set objMyRecordset = Nothing
MsgBox "Address: " & vbCrLf & strAddree & vbCrLf & strAddl1 & vbCrLf & strAddl2 & vbCrLf & strAddl3 & vbCrLf & strAddl4 & vbCrLf & strAddpc
End Sub