• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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:
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
        If Dir(sTxtPath) > vbNullString Then Kill sTxtPath
        If Nz(AutoExport, True) = False Then MsgBox "No Chargeback Records to Export.", vbOKOnly, "Record Export"
        sTxtPath = ""
        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") & ")")
                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 & ")) ")
        Set rs = Nothing
    End If
    Echo True
    ExportDataFile = sTxtPath
End Function

Open in new window

Thanks ahead of time for your assistance.
1 Solution
Rey Obrero (Capricorn1)Commented:
try changing this  

 '" & sComment4 & "'


" & Chr(34) & sComment4 & chr(34) & "
TheGLAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Gustav BrockCIOCommented:
Yes, nothing "French" here, only malformed data.

However, you could try:

strComment4 = CStr([Comment4])

TheGLAuthor Commented:
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 BrockCIOCommented:
OK, then you are left with a manual edit of the data.

TheGLAuthor Commented:
Found that the user in charge of the source database had some code to "handle" characters with accents that was producing garbage characters.  Changed his code to something similar to what was found here.

Thanks all for taking a shot at this one.
TheGLAuthor Commented:
Not exactly sure if I should be handing points out on this due to my limited experience in doing so.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now