Remove quote marks from imported table

[treva26]

I am using DoCmd.TransferText to import a CSV file to a new table.
The problem is some of the fields contain quotation marks which really messes things up for me later on.
Is there some way I can go through the whole table and remove them?

Maybe a Query?
Or VBA?

 

[David Eagar]

Have you tried a simple Search & Replace?

 

[gemma-the-husky]

if you use a file specification to do the import, i think this can be set to automatically remove the text qualifiers

 

[treva26]

I need to do it in VBA, can I do search & replace with that?

The importing does remove the text qualifiers, but there are sometimes extra quote marks within the fields.

 

[treva26]

It is removing the text qualifiers, but sometimes there are quote marks within the fields.

Can I do a search and replace in VBA for characters within fields?
I guess it would need to check each character individually.

 

[treva26]

Finally found what I needed :

Function FindReplace(strOrig As Variant, strOld As String, strNew As String)
'Function to search and replace characters in a string
'-----------------------------------------------------------
'   ARGUEMENT               DESCRIPTION
'   --------------------------------------------------------
'   strOrig                 String in which to
'                           search/replace.
'
'   strOld                  String you are searching for.
'
'   strNew                  String to replace the searched
'                           for string.
'   --------------------------------------------------------
'   EXAMPLE
'   --------------------------------------------------------
'   MyString = "555.318.6755"
'   MyNewString = FindReplace(MyString,".","-")
'   (MyNewString is now "555-318-6755")
'-----------------------------------------------------------

Dim intAt As Integer, strAltered As String

'Check for arguements
    If IsNull(strOld) Or IsNull(strNew) Then
        FindReplace = "ERROR! CHECK ARGUEMENTS!"
        Exit Function
    End If
    
'Check for null string
    If IsNull(strOrig) Then
        FindReplace = Null
        Exit Function
    End If
   
'Do function
    For intAt = 1 To Len(strOrig)
        If Mid(strOrig, intAt, Len(strOld)) = strOld Then
            strAltered = strAltered & strNew
            intAt = intAt + (Len(strOld) - 1)
        Else
            strAltered = strAltered & Mid(strOrig, intAt, 1)
        End If
    Next intAt
    FindReplace = strAltered

End Function

So I just have a DAO recordset loop that goes thru each field and does this:

rs1!f1 = FindReplace(rs1!f1, """", "-")

 

[gemma-the-husky]

which version of access are you using - there is an easy to use replace function in later versions

 

[treva26]

Im using Access 2003

 

[gemma-the-husky]

replace function is available in that

you can simply replace("""","")

which will replace a dble quote mark with a blank

 

[treva26]

oh, haha

thanks