Concatenating address fields skipping blanks (1 Viewer)

[peskywinnets]

Concatenating address fields but skipping blank fields

Ok, I've tried (I really have) & googled (a lot), but I'm not quite getting it!.

I seek to Concatenate address fields in a query, skipping those fields that are blank.

At first I was using the mother of all nested iif(), but frankly it was getting unwieldly, I then established I should be making use of the + operator instead....

ShippingAddress: [ShippingName]+Chr(13) & Chr(10)+[ShippingAddress1]+Chr(13) & Chr(10)+[ShippingAddress2]+Chr(13) & Chr(10)+[ShippingCity]+Chr(13) & Chr(10)+[ShippingRegion]+Chr(13) & Chr(10)+[ShippingPostcode]

....it sort of works but my query output still has a blank lines embedded. For example if someone hasn't entered a ShippingAddress2 in their Shipping Address (or a ShippingRegion ...what some would refer to as State), I get this output...

Joe Brown
1 Acacia Terrace

London

N12 7YF

...so how do I change the above code so that the output would look like this (i.e. no empty/blank lines)...

Joe Brown
1 Acacia Terrace
London
N12 7YF

the main culprit shipping address fields that are quite often blank are ShippingAddress2 (shipping address line 2), ShippingRegion & ShippingPostcode (e.g. many parts of Ireland still don't have a postcode)

 

[sneuberg]

I wonder if parenthesis would help. For example if you enclose this part

Chr(10)+[ShippingAddress2]+Chr(13)

in parenthesis like

(Chr(10)+[ShippingAddress2]+Chr(13))

certainly everything withing the parenthesis would have to disappear if [ShippingAddress2] is null.

But the other question is, "Is it null or an empty string?". You need to make sure empty strings are not being injected into your data. I see forum member's code like. Me.txtSometextbox = "" to clear a textbox. That's just asking for trouble.

You can check your data for empty strings with a query that has "" as criteria for the field in question.

Finally I suggest using the constant vbCrLf in place of Chr(13) and Chr(10). Chr(10) is newline in UNIX, Chr(13) is newline for MAC. Read this for more info.

 

[plog]

Are you ultimately using this data in a Report Object? If so, don't worry about blanks, simply layout the report as if all boxes are filled in, then on the properties of each box go to the Format tab and set the 'Can Grow' and 'Can Shrink' properties to Yes. It will do the hard work for you.

 

[peskywinnets]

I wonder if parenthesis would help. For example if you enclose this part

Chr(10)+[ShippingAddress2]+Chr(13)

in parenthesis like

(Chr(10)+[ShippingAddress2]+Chr(13))

certainly everything withing the parenthesis would have to disappear if [ShippingAddress2] is null.

But the other question is, "Is it null or an empty string?". You need to make sure empty strings are not being injected into your data. I see forum member's code like. Me.txtSometextbox = "" to clear a textbox. That's just asking for trouble.

You can check your data for empty strings with a query that has "" as criteria for the field in question.

Finally I suggest using the constant vbCrLf in place of Chr(13) and Chr(10). Chr(10) is newline in UNIX, Chr(13) is newline for MAC. Read this for more info.

The parenthesis didn't work, but that may be because it transpires ShippingAddress2 (& no doubt other fields) is indeed an empty string "" & not a null (thanks for the tip). Point taken about the use of vbCrLf ...I will use that instead

Are you ultimately using this data in a Report Object? If so, don't worry about blanks, simply layout the report as if all boxes are filled in, then on the properties of each box go to the Format tab and set the 'Can Grow' and 'Can Shrink' properties to Yes. It will do the hard work for you.

Yes, ultimately this will be used in a report (a report to spit out a shipping label for the package)....that's useful info

I'll tidy up the data in my tables to nulls tomorrow as it's late here in London & bed calls - thanks for your input

 

[peskywinnets]

Hmm, still beat here.

I have set the 'can grow'/'can shrink' setting in the report properties (for both the data & the detail) but it didn't work (blank lines still showing)...I'm figuring it's because the blank lines are embedded with the concatenated query data that the report treats this concatenated data as one bit of data?

The route that works is handing all the individual unconcatenated fields to the report .....& then using can grow/can shrink in the report. However, I wanted to have the all this data 'grouped' so the font size could be configured to automatically fill the available shipping label space, but if I try to do this with this solution that works, I may end up with different address lines in different font sizes?

 

[sneuberg]

I suggest forgetting about the concatenated expression for the moment and try the wizard for making labels. It's in the Reports section of the CREATE tab. This will create labels from the fields. It does the concatenation for you where it is needed and sets it up so that blank lines are suppressed. I think you might be able to take what it creates and incorporate it into your report.

 

[peskywinnets]

I suggest forgetting about the concatenated expression for the moment and try the wizard for making labels. It's in the Reports section of the CREATE tab. This will create labels from the fields. It does the concatenation for you where it is needed and sets it up so that blank lines are suppressed. I think you might be able to take what it creates and incorporate it into your report.

Hi Steve,

thanks...that's the path I'm going to have to take (as it works, whereas my original proposed way had blank lines)....but the problem is customers' shipping address line lengths are all over the place (some are very short & some are very long) ....with my original way, I could treat the data as one entity (& therefore get the font to change dynamically to fill the available sticky label space), whereas going your suggested way, all the shipping address fields will be 'standalone' in the report so to speak....therefore to cater for the longest expected shipping address lines, I will have to make the font small for all shipping address lines.