Re: Removing quotation marks

From: fredg (fgutkind_at_example.invalid)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 21:00:13 GMT

On Tue, 14 Sep 2004 12:21:52 -0700, Mark wrote:

> 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. 

-- 
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Relevant Pages

  • Re: Simple Question
    ... If the field you are applying criteria is a number field then no quotes ... If the field is a text field that contains numeric characters then quotes ... I just had to fix a query my boss was writing. ... All I did was remove the quotation marks from the numbers and the query ...
    (microsoft.public.access.queries)
  • Re: Subform Requery Doesnt Show Updates to Data
    ... I did not bind the main form to a query and I ... Private Sub cmdClose_Click ... Get rid of the DoCmd.RunCommand acCmdSaveRecord line. ... About the only other idea I have about the delay is maybe ...
    (microsoft.public.access.formscoding)
  • Re: CONVERT
    ... the dynamic SQL using a table-valued UDF: ... datatypes are doing what in this query. ... want to get rid of the nulls, use COALESCE -- look it up in BOL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Date Query
    ... Query one to get FirstDate in Month for each RID ... requested input value for year ... an expression that will tell me the FIRST Weight in January (of the ...
    (microsoft.public.access.queries)
  • Re: Tips to speed up query with aggregate functions on millions of
    ... How can I get rid of the date function? ... Get rid of the functions in your group by and order clause so that the ... covering) for queries that hit more than 1-2% of the total rows in a table. ... If your large query is doing index seeks and bookmark lookups, ...
    (microsoft.public.sqlserver.programming)