Strip field data of quotation marks

[B. Meincke]

I have a csv text data that contains address information that has been
concatenated in an external query and imported into our database using the
TransferText method. The problem is that some of the records include PO box
numbers and Unit numbers so the addr_line2 field value contains a comma. This
ends up splitting the field into two and opening the first field with a
quotation mark and closing the second field with a quotation mark. I hope
this makes sense.

For example:

(addr_line2 in the source query)
123 Somewhere Street
becomes...
(addr_line1 in the destination query) (addr_line2)
123 Somewhere Street (is null)

(addr_line2 in the source query)
456 Nowhere Street, Unit 789
becomes...
(addr_line1 in the destination query) (addr_line2)
"456 Nowhere Street Unit 789"

Long story short, how the text is imported is out of my hands, but is there
any way I can write an UPDATE query that would select the records with
quotation marks in the address fields and strip the quotation marks off the
affected values?

I read a thread here that suggests a way to concatenate the addition of
Chr(34) and I understand the syntax behind that (although not the reason for
wanting to do it) and I've tried using the following:

UPDATE tblData
SET addr_line1 = addr_line1-Chr(34);

But that reports an error.

Any suggestions or advice would be appreciated. Thanks in advance.

 

[Allen Browne]

Is there a chance to massage the data before import? For example, open in
Word, seach'n'replace " with (say) zz or a half-symbol (Alt + 171 on numeric
keypad.) That way the data won't split into 2 records and mess up columns on
import.

Alternatively, you could write a VBA routine to do this before importing.
Open the file, Print # to a new one, Close, Kill the old one, and Name the
new one.

 

[B. Meincke]

Thank you for your reply, Allen.

No, I'm afraid I have no control over the imported text. I am left to work
with it as is. In my limitted scope of knowledge, the only solution I could
think of was to find a method to strip the quotation marks out of the Access
table data after the TransferText. As barbaric as this might seem, is there a
way to do this?

My long term goal is to automate the daily table data updates but I don't
think this is realistic anymore.

Thanks again, as always, to all you guys for your continued support.
--
BJM
ACE Assistant
Gary Allan High School

Is there a chance to massage the data before import? For example, open in
Word, seach'n'replace " with (say) zz or a half-symbol (Alt + 171 on numeric
keypad.) That way the data won't split into 2 records and mess up columns on
import.

Alternatively, you could write a VBA routine to do this before importing.
Open the file, Print # to a new one, Close, Kill the old one, and Name the
new one.

 

[John Spencer]

Use two update queries.

Strip leading quote

UPDATE YourTable
Set AddrLine1 = Mid([AddrLine1],2)
WHERE AddrLine1 Like """*"

Strip Trailing quote
UPDATE YourTable
Set AddrLine2 = Left([AddrLine2],Len(AddrLine2)-1)
WHERE AddrLine2 Like "*"""

If you can only build queries in query view then here is a hint on doing the
first query.
-- Start a new query
-- Add your table
-- Add the AddrLine1 field
-- Set its criteria to
Like """*"
-- Select Query: Update from the menu
-- In the update to field enter
Mid([AddrLine1],2)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County