Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Handling French(?) Characters in a String

Posted on 2014-01-29
8
Medium Priority
?
474 Views
Last Modified: 2014-02-04
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
        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

Open in new window



Thanks ahead of time for your assistance.
0
Comment
Question by:TheGL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39819502
try changing this  

 '" & sComment4 & "'

with

" & Chr(34) & sComment4 & chr(34) & "
0
 

Author Comment

by:TheGL
ID: 39819588
Unfortunately I still receive the error when adding chr(34).
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39819999
why should LTEE became LTE??E  ?
did you write in french ?
you should compact and repair your database, something is corrupted there
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39820103
Yes, nothing "French" here, only malformed data.

However, you could try:

strComment4 = CStr([Comment4])

/gustav
0
 

Author Comment

by:TheGL
ID: 39820841
Gozreh - I have compacted both the source database (also an access DB) and this one, no luck.

Gustav - using CSTR yields the same result.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39820851
OK, then you are left with a manual edit of the data.

/gustav
0
 

Accepted Solution

by:
TheGL earned 0 total points
ID: 39822932
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.
0
 

Author Closing Comment

by:TheGL
ID: 39832017
Not exactly sure if I should be handing points out on this due to my limited experience in doing so.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 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