RETURN in a string

[John MilburySteen]

I'm using A2000.

Field1 always has data; Field2 may be NULL.

In a report I want a control to be

=Field1 & IIF(IsNull(Field2), Field2, <RETURN> & Field2)

I want to do it this way so that if Field2 is NULL it doesn't generate a
blank line in my report.

<RETURN> means a carriage return -- but how do I insert the return? Do I
use the Chr function?


Is there any other way to suppress a return if you're printing a field that
is NULL?

 

[John Vinson]

<RETURN> means a carriage return -- but how do I insert the return? Do I
use the Chr function?

It's a bit peculiar. A <RETURN> is actually TWO ASCII characters -
Chr(13) and Chr(10), *in that order* (carriage return followed by
linefeed). So

=Field1 & IIF(IsNull(Field2), Field2, Chr(13) & Chr(10) & Field2)

Is there any other way to suppress a return if you're printing a field that
is NULL?

Several actually. Simplest on a Report is to bind the field to a
textbox and set that textbox's Can Grow and Can Shrink properties to
True. It will vanish and close up controls further down the page if
the field is NULL.

John W. Vinson[MVP]

 

[John MilburySteen]

Thanks, John -- yet again. (You have helped me before.)

 

[TC]

Just in case you need them in future:

vbCR <CR> Chr$(13)
vbLF <LF> Chr$(10)
vbCrLf (guess!)

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[John Vinson]

Just in case you need them in future:

vbCR <CR> Chr$(13)
vbLF <LF> Chr$(10)
vbCrLf (guess!)

Thanks TC - should have mentioned these! However... These VBA
constants work in VBA code but not in SQL queries.


John W. Vinson[MVP]