Trim like an excel mofo

[armitage]

Hi there.

I have a little problem.

I have a file that I split into little code files each with their own little Code and description. Now whoever the smart one was that is making these codes and descriptions has put double spaces between some descriptions and before and after some others.

Now in excel I don't have a problem removing them. I just use the good old Trim() function. But in access like most other programs the trim function just removes the leading and trailing spaces but leaves the double spaces inside the actual strings there.


Now my question is this. Is it possible to write a query or macro that would inspect each description and search for double spaces and either remove them if their on the end/beginning of a string or replace them with a single space if they are in the inside of a string.

Basically a search and replace function i guess is what im looking for here.

 

[stopher]

I think the REPLACE function is what you are looking for. Check out Access Help for the exact usage. So a combination of REPLACE and TRIM should used in a query should do the trick.
Stopher

 

[armitage]

Woot

Thanks stoph. Ure a life saver.

Up to now i've had to use excel to import the tables from access then use the excel trim function. After that I had to reimport into access and go further with it.
What a nub I am.
Hopefully ill get smarter


Btw i know this is only my 5th post but damn i love this site. It's really helping me become much better at access and not feel so nubby

 

[armitage]

Well I tried the replace method but it seems to not work as intended. Im still finding doublespaces in my descriptions.
I combined everything into one query so that I don't have to run extra queries afterwards to do the description modification.

sdtldesc:Replace(Trim([2_Link_Seg]!sdtldesc),"  "," ")

Any idea why im still getting double spaces?

 

[stopher]

Well I tried the replace method but it seems to not work as intended. Im still finding doublespaces in my descriptions.
I combined everything into one query so that I don't have to run extra queries afterwards to do the description modification.

sdtldesc:Replace(Trim([2_Link_Seg]!sdtldesc),"  "," ")

Any idea why im still getting double spaces?

Are there likely to be instances of three spaces? The function above will turn three spaces into two.
Stopher

 

[armitage]

" " is a doublespace which is converted to " " a single space. Am I getting it wrong somehow?

 

[stopher]

" " is a doublespace which is converted to " " a single space. Am I getting it wrong somehow?

The function is fine for how you describe your problem. The point I was making is that if there are three spaces between two words in your string, then the function will take the first two spaces a replace them with a single space leaving the third space untouched. So where there are three spaces they will become two. Do you think there are any occurences of three (or more) spaces?

Perhaps you could post a simple version of your database with some sample data that isn't working plus the query.

Stopher