Exclude carriage returns from expression if they are not needed?

[jdusn1978]

Hello,

Note: Can not use code on my system. Disabled Mode will not be enabled for me.

My query pulls data that requires carriage returns if there’s data, and no carriage return if there is none. How do I write a concatenating expression with carriage returns, but skip the carriage return if no data precedes the carriage return expression. I’m asking in order to eliminate white space in the query return due to a poorly written expression.

Currently written as [Expr1] + Chr(13) & Chr(10) + [Expr2] + Chr(13) & Chr(10) + etc because I was under the impression that + meant “optional”.

But, I need if [Expr1] is Null then skip the carriage return.

Thank You!

[TheSmileyCoder]

You need to be clearer in your instructions to the machine. At current you are getting:
[Expr1] + Chr(13) & Chr(10)
evaluating to
Null & chr(10)
evaluating to
chr(10)
so instead, use:
([Expr1] + Chr(13) + Chr(10)) & ([Expr2] + Chr(13) + Chr(10))
By adding the paranthesis (changing a + to a & and a & to a +), we are now changing the order of evaluation of the expressions.

Hope that helps.

[PhilOfWalton]

I think You're missing the point. The carriage returns go with Expr2, not Expr1.
So you need something like
FinalExpr = IIf(Nz(Expr1) > "", Expr1) & IIf(Nz(Expr2) > "", Chr$(13) & Chr$(10) & Expr2) & IIf(Nz(Expr3) > "", Chr$(13) & Chr$(10) & Expr3)
etc etc.
So see if ExprX has a value, and only if it exists, add the preceding line feeds and ExprX

Phil

[NeoPa]

Hi. It looks to me as if you're struggling with a complicated expression and making a couple of minor errors in there. Hence dealing with it needs careful consideration of a number of the parts.

I'll start by pointing you towards an article (Using "&" and "+" in WHERE Clause) that explains the concept of using + and &. TBF you had the basic idea but were a little off on the details.

Now, as Smiley explains in his post, only those items to the immediate left and right of the + are examined to determine if the expression should resolve to Null or not. That means, if you want a carriage return / line feed pair to be optional depending on an expression then the carriage return / line feed pair must be enclosed within parentheses. Otherwise it only operates on the CR and leaves the LF in place.

Another important point to remember is that '' (or even "" if you're using Access' default quote char) is quite different from Null. The first is an empty string. The second isn't even of type string at all, and can only be used with strings when coerced. So '' + (Chr(13) & Chr(10)) resolves to Chr(13) & Chr(10), whereas Null + (Chr(13) & Chr(10)) resolves to Null.

A last point is that form and report controls typically have a value of Null when empty. Fields (String), on the other hand, can have either Null or empty string ('') depending on its attributes (.Required & .AllowZeroLength). So it's important to remember what you're dealing with. In your case it seems to be fields rather than controls.

Assuming your empty fields contain Nulls instead of empty strings you'll need something like :

[Expr1] + (Chr(13) & Chr(10)) & [Expr2] + (Chr(13) & Chr(10)) & [Expr3] + (Chr(13) & Chr(10)) & ...

If the values are empty strings instead :

[Expr1] & IIf([Expr1]>'',Chr(13) & Chr(10),'') & [Expr2] & IIf([Expr2]>'',Chr(13) & Chr(10),'') & ...

Notice this is more clumsy so I'd consider setting your fields up to contain Nulls when empty if you can.

[NeoPa]

Hi Phil.

While it probably doesn't matter whether the new line characters are dropped depending on the previous or subsequent expressions, the OP's original code seems to indicate their intention of linking it to the previous.

As I say though, at the end of the day both work perfectly well.

[jdusn1978]

NeoPa,
Your answer not only fixed this problem for me, but also led to answering a bunch of other problems I was having. I now have a better understanding of these above issues.

Thank you for your time Sir!

And thanks to all of you for doing what you do here. Much appreciated!

[NeoPa]

I'm very pleased to be able to help.

I've also made a note of your kind post to show to my MVP lead next time my award comes up for renewal, so that's helpful for me too :-)