String comparison

[RWI]

I know this can be done, but I can't seem to get it to work, if I have this
data:

NUMBER NAME DIRECTION STREET
123 MAIN ST
28 LILAC W LN

It would look like this:
123 MAIN ST
28 W LILAC LN

Where if there is data in the columns it would populate and if it is blank
or null it would go on to the next column and not populate a blank space.

Any help?

 

[M.L. Sco Scofield]

A little more info would have been nice. Like, where are you trying to do
this? I'm going to make a wild guess and say that it's in a report.

There is a little know behavior of the plus sign (+) in Access when used for
concatenating strings. It's called null propagation. (See the help file.)

In the Control Source property of a text box on your report, you can put:

=[NUMBER] & " "+[NAME] & " "+[DIRECTION] & " "+[STREET]

If NAME, DIRECTION, and/or STREET are Null, this will cancel out (null
propagate) the preceding space.

The long way will require a bunch of IIf's (very ugly) or writing a little
VBA function.

If you are trying to do this in a query and not a report, replace the equal
sign (=) with an alias. (Address: would be good.)

BTW, make sure your text box's name is something besides one of these filed
names. (txtAddress would be a good name.)

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[John Vinson]

I know this can be done, but I can't seem to get it to work, if I have this
data:

NUMBER NAME DIRECTION STREET
123 MAIN ST
28 LILAC W LN

It would look like this:
123 MAIN ST
28 W LILAC LN

Where if there is data in the columns it would populate and if it is blank
or null it would go on to the next column and not populate a blank space.

You can use the fact that both & and + concatenate strings - but &
treats NULL as a zero length string, while + propagates NULLs (so
[Direction] + " " will be NULL if Direction is NULL).

Try

[Number] & (" " + [Name]) & (" " + [DIRECTION]) & (" " + [STREET])


John W. Vinson[MVP]