Need help to format text field in ACCESS data table

jjxia2001
jjxia2001 used Ask the Experts™
on
I have a field in an Access table to show three quotes such as this: """0-2,500""". I want to change it to "0-2,500".  How do I delete the extra ".  The length in the middle is not same, may have """2,500-5,000""" or """5,000-10,000""".

I tried MID function, but need the length: MID(fieldName,3,length), length is varied.

Please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try replace(replace(yourField, chr(34)chr(34), chr34), chr(34)chr(34), chr(34))

Author

Commented:
Got invalid syntax error.
Top Expert 2010
Commented:
Or...

SELECT [SomeColumn], Replace([SomeColumn], """""""", """") AS Replaced
FROM [SomeTable]

UPDATE [SomeTable]
SET [SomeColumn] = Replace([SomeColumn], """""""", """")
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
Try replace(replace(yourField, 'chr(34)chr(34)', 'chr34'), 'chr(34)chr(34)', 'chr(34)')
Top Expert 2010

Commented:
jerryb30,

I think you are missing some concatenation operators :)

Patrick
Top Expert 2010

Commented:
jjxia2001,

Did you try either of mine?

Patrick
Commented:
I was wondering. I am doing air code.

Try replace(replace(yourField, chr(34) & chr(34), chr(34)), chr(34) & chr(34), chr(34))
Maybe a missing paren, too.

Thanks.

And patrick's works just fine, too. :)
using your original solution you could also do :

MID(fieldName,3,length(fieldName)-4)

Author

Commented:
All three ways worked, but I like the one from buttersk.  Thanks all!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial