Rtrim and Ltrim with values

[Jai_Friday]

Morning All,

Is it possible to trim reoccurring symbols of values from a field.

I have combined 10 fields with '/' seporators, the 10 fields may or may not
contain values so if the first 2 fields have values the end field shows

123/456////////

What I wish to do is run a function like the trim function (unless you can
pass values through it) so instead of triming blanks spaces it trims the /

So in aware another way to ask the question it...

Can the RTrim function remove symbols rather than the default spaces??

Thanks in Advance

Jai

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

 

[Allen Browne]

If you want to trim all the slashes out, use the Replace() function.

If you want to parse the field in code, determining which of the 10
positions lack data, use the Split() function.

Hopefully your ultimate goal is to have related records each with a single
value, rather than have 10 values in the one field. Atomicity is one of the
goals of normalization.

 

[Jai_Friday]

Thanks for the feedback, Sorted..

I passed the initaily string through a replace() - to replace / 's for
spaces,
then Rtrim() the string and ran a second replace to replace spaces for / 's
which got rid of the end Junk

Thanks Again

Jai

 

[John Spencer]

You might consider NOT adding the slashes at all when you build the string.

One way to do this is use the + concatenation operator in conjunction with
the & concatenation operator.

Field1 + "/" & Field2 + "/" & Field3 + "/" & ... & Field10

That will only add a slash when the preceding field is not null. You could
also use
IIF(Field1 is not null,Field1 & "/") & IIF(Field2 is not null, Field2 & "/")
& ...

Or if the fields could contain null or zero-length string
IIF(Field1 & "" <> "" ,Field1 & "/") & IIF(Field2 &"" <> "", Field2 & "/")
& ...

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..