TheGL
asked on
Handling French(?) Characters in a String
Hello Experts,
I've been working on a VBA procedure in my Access database that will write some data from a query to a text file. One of the fields (Comment4) from time to time will come down with what I suspect to be some French characters. When I try to write Comment4 from the recordset to the file, I receive the error "Invalid procedure call or argument."
A sample of the data that has thrown an error:
PLOMBERIE P.R.P LTEE (This is how it appears in the source table)
PLOMBERIE P.R.P LTE??E (This is how it appears if I open a recordset and try to manipulate the field in VBA)
P L O M B E R I E P . R . P L T E ƒ ‰ E (This is how it appears if I do a StrConv to Unicode)
In the latter 2 cases, I've tried doing a replace on those strings with no luck.
Below is the code:
Thanks ahead of time for your assistance.
I've been working on a VBA procedure in my Access database that will write some data from a query to a text file. One of the fields (Comment4) from time to time will come down with what I suspect to be some French characters. When I try to write Comment4 from the recordset to the file, I receive the error "Invalid procedure call or argument."
A sample of the data that has thrown an error:
PLOMBERIE P.R.P LTEE (This is how it appears in the source table)
PLOMBERIE P.R.P LTE??E (This is how it appears if I open a recordset and try to manipulate the field in VBA)
P L O M B E R I E P . R . P L T E ƒ ‰ E (This is how it appears if I do a StrConv to Unicode)
In the latter 2 cases, I've tried doing a replace on those strings with no luck.
Below is the code:
Function ExportDataFile(Optional ByVal AutoExport As Boolean, Optional ByVal bViewFile As Boolean) As String
Dim rs As Recordset
Dim fs, textfile
Dim sTxtPath As String
Dim sUserID As String
Dim sComment4 As String
bViewFile = Nz(bViewFile, False)
'Set File Names
sTxtPath = CurrentProject.Path & "\FTP_File\ChargebackPins_" & Format(Date, "mmddyyyy") & ".txt"
Set fs = CreateObject("Scripting.FileSystemObject")
If Dir(sTxtPath) > vbNullString Then Kill sTxtPath
If Dir(sTxtPath) = vbNullString Then Set textfile = fs.CreateTextFile(sTxtPath, True)
Set rs = CurrentDb.OpenRecordset(Export_ChargebackPins)
If rs.RecordCount = 0 Then
textfile.Close
If Dir(sTxtPath) > vbNullString Then Kill sTxtPath
If Nz(AutoExport, True) = False Then MsgBox "No Chargeback Records to Export.", vbOKOnly, "Record Export"
sTxtPath = ""
Else
rs.MoveLast
rs.MoveFirst
Echo False
Do Until rs.EOF
If Nz(rs![UserID Auditor], "") = "" Then
sUserID = DLookup("[cnh_userID_]", "tbl_UserList", "[defaultadmin]=-1")
sComment4 = Replace(rs![Comment 4], "()", "(" & DLookup("left([first_name],1)", "tbl_UserList", "[defaultadmin]=-1") & DLookup("left([last_name],1)", "tbl_UserList", "[defaultadmin]=-1") & ")")
Else
sUserID = rs![UserID Auditor]
sComment4 = StrConv(rs![Comment 4], vbUnicode)
End If
textfile.WriteLine rs![Record Code] & "|" & rs![Auth No] & "|" & Format(rs![Auth Date], "yyyymmdd") & "|" & rs![Dealer No] & "|" & rs![Invoice No] & _
"|" & rs![Orig Pin] & "|" & rs![Appr Pin] & "|" & rs![Appr Amt Sign] & "|" & Format(rs![ApprPinAmt], "0.00") & "|" & rs![Comment 1] & _
"|" & rs![Comment 2] & "|" & rs![Comment 3] & "|" & sComment4 & "|" & sUserID
If bViewFile = False Then DoCmd.RunSQL ("UPDATE tbl_AuditLogMaster AS x SET x.ExportedForESS = #" & Date & "# WHERE (((x.AuditLogID)= " & rs!AuditLogID & ")) ")
If Nz(rs![UserID Auditor], "") = "" Then DoCmd.RunSQL ("UPDATE tbl_AuditLogMaster AS x SET x.[UserID Auditor] = '" & sUserID & "', x.[comment 4] = '" & sComment4 & "' WHERE (((x.AuditLogID)= " & rs!AuditLogID & ")) ")
rs.MoveNext
Loop
textfile.Close
rs.Close
Set rs = Nothing
End If
Echo True
ExportDataFile = sTxtPath
End Function
Thanks ahead of time for your assistance.
ASKER
Unfortunately I still receive the error when adding chr(34).
why should LTEE became LTE??E ?
did you write in french ?
you should compact and repair your database, something is corrupted there
did you write in french ?
you should compact and repair your database, something is corrupted there
Yes, nothing "French" here, only malformed data.
However, you could try:
strComment4 = CStr([Comment4])
/gustav
However, you could try:
strComment4 = CStr([Comment4])
/gustav
ASKER
Gozreh - I have compacted both the source database (also an access DB) and this one, no luck.
Gustav - using CSTR yields the same result.
Gustav - using CSTR yields the same result.
OK, then you are left with a manual edit of the data.
/gustav
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not exactly sure if I should be handing points out on this due to my limited experience in doing so.
'" & sComment4 & "'
with
" & Chr(34) & sComment4 & chr(34) & "