Clean up values

[Legacy 15162]

I had to link a file using a fixed width. It was the only viable solution at the time. Unfortunately this leaves some text that I need removed from my values. there are spaces and a | in the columns that I want to return. The field name currently is Plan Sales:[Field14]. How can I get rid of these spaces and | ? I need to just see the monetary value.

 

[bat17]

Can you post a couple of samples of the data so we can see the format of it?

peter

Ps. What version of Access are you using?

 

[Legacy 15162]

Field 14
$230.34| <-- note the preceding spaces
$123.99| <---- note the pipe

I believe we are on 2000

 

[bat17]

I dont have Access 2000 to check on but I think that this will work.
Sales: trim(Replace([Field 14],"|",""))
If you need to force it into a curency data type then you can stretch it a bit further
Sales: CCur((Trim(Replace(Replace([Field 14],"$",""),"|",""))))

HTH

Peter

 

[Legacy 15162]

Undefined function 'Replace' in expression......Should I write a VBA function, or does access have one built in that I am just missing?

 

[bat17]

It could be a reference problem or the fact that I have A97 and A2002 on this machine. I will try it tonight at home where I have A2000.

Peter

 

[bat17]

I have just tried it at home and it runs fine on A2000 here with no extra references selected.

Possiblly you have A97 which dosent have the 'replace' function.

I am not back to work to Tuesday now so maybe someone else can help with code that will work in A97

Peter

 

[Legacy 15162]

much appreciated kind sir!

 

[bat17]

WOW, Your quick

peter

 

[Legacy 15162]

too bad i'm not as quick as i'd like to be at picking this stuff up.