Need help Access

[Oz1127]

I have a thread that was answered before on this subject, but I've tried everything and my results are just not right. My question is: How do I get rid of blank space in a field(that I can't tell is there), and how do I get rid of quotation marks that preceeded and trailing data within the field. I need to get rid of this,I've tried trimming with every example I've found on the net but nothing seems to work right. I always end up trimming the data I want to keep. Any help would help, Thanks in advance.

 

[PHV]

Can you please post some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Oz1127]

Empid FirstName LastName Supervisor
"george adams" ""
"" kelly Bundy" Dave Doe
"" "Lisa Kudrow" "Billy Joe"

This is how the table looks after it's imported, some fields have blanks, quote marks, or leading/trailing quote marks and the table I'm joining it to in the query has fields padded with blanks too. I think this is really screwing up my results because out of 300+ records I only match on 18 and all of the records are in both tables. I need to get back the "Empid" for all the records.Table A is like above and table B contains the empid.Hope this helps you help me, Thanks

 

[PHV]

Depending of your version of access you may try this as a starting point:
UPDATE yourTable
SET yourField = Trim(Replace(yourField, '"', ''))
WHERE yourField Like '*"*';

And then many times:
UPDATE yourTable
SET yourField = Replace(yourField, ' ', ' ')
WHERE yourField Like '* *';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Oz1127]

Thank PHV, I'll try that and see if it works this morning. I appreciate your quick response.