MiddleValues trailing return character

[Jim Lah]

I have just tracked down spurious behaviour in my database to the MiddleValues function. Using it to extract a single value out of a list results in the single value plus a trailing return character.

Whilst this can be removed with substitute function I was wondering if this is deliberate behaviour of MiddleValues as it does not seem consistent with the function description.

Can anyone advise me?

Regards

Jim

[Lee Smith]

Not sure about this being the answer, but I would try using Trim4(Yourcurrentcalc)

Trim4 is a Custom Function you can get Here

[Vaughan]

The trailing paragraph is expected behaviour with the LeftValues(), MiddleValues() and RightValues() functions. Documented, too.

Lee's suggestion of the Trim4() function is my standard solution.

[Jim Lah]

Thanks both.

You are right, it is documented now I read it (twice ) again !

Jim

[comment]

All xValues() functions return a result followed by a trailing carriage return. To extract a single value out of a list, you'd be better off using GetValue().

[LaRetta]

All of the xValues include the trailing return and the idea is that you can extract multiple values without having to insert carriage returns.

Since xValues usually returns single or specific item(s), and if you want to concatenate them, you would need to use an If() statement in the middle to decide whether or not to place a return in the middle of both lists. If either list is empty, the extra leading carriage return would corrupt your list.

So I think the idea is ... you are working with lists and a carriage return follows every list data-line so it has to be one way or the other for consistency. A trailing CR isn't an issue ... ValueCount() skips trailing. But somewhere, somehow (when you least expect it), you'll have to decide whether you want a CR or not. With all xValues including a carriage return, it seems safest than removing it later.

At least that's my theory and I'm sticking to it.

OOOPS ... I was typing slowly attempting to formulate my words properly (for a change), Michael, and I didn't see your post. :blush2:

Edited June 10, 2009 by Guest
Added update