Removing quotation marks

[Mark]

I have data that is imported in with quotation marks
around each field in a record. Is there a way to write a
query that gets rid of the quotation marks? any help is
appreciated as always.
How it is Desired
Field1 Field2 Field1 Field2
"ABC" "123" ABC 123

 

[Tonín]

SELECT Mid([Field1],2,Len([Field1])-2) AS Field1New,
Mid([Field2],2,Len([Field2])-2) AS Field2New
FROM YourTable;



Tonín

 

[fredg]

I have data that is imported in with quotation marks
around each field in a record. Is there a way to write a
query that gets rid of the quotation marks? any help is
appreciated as always.
How it is Desired
Field1 Field2 Field1 Field2
"ABC" "123" ABC 123

A permanent change to the data?
Create an Update Query.
If your version of Access has the Replace() function you can use:

Update YourTable Set YourTable.[Field1] = Replace([Field1],'"',""),
YourTable.[Field2] = Replace([Field2],'"',"");

With spaces added for clarity only, the quotes are as follows:
Replace([FieldName],' " ' , " ")

Note: Some earlier versions of Access 2000 have the Replace() function
but it is not usable directly in a query.
You can create a function in a module using the Replace function there
and return the new value to the query.

 

[John Vinson]

I have data that is imported in with quotation marks
around each field in a record. Is there a way to write a
query that gets rid of the quotation marks? any help is
appreciated as always.
How it is Desired
Field1 Field2 Field1 Field2
"ABC" "123" ABC 123

Doable, but the technique varies a bit with your version of Access.
With AccessXP or later, you can run an Update query updating Field1 to

Replace([Field1], '"', '')

using single quotes to delimit the doublequote character. With A2000
you can't use the Replace function directly in a query - you'll need
to write a silly little wrapper function like

Public Function QReplace(strIn AS String, strOld As String, _
strNew As String) As String
QReplace = Replace(strIn, strOld, strNew)
End Function

and call it in your update query instead of Replace.

John W. Vinson[MVP]
(no longer chatting for now)