BLANK LINES IN NAME ADDRESS FIELD

[gearhead03]

I HAVE A REPORT THAT PRINTS INVOICES. THE CUTOMERS ARE ENTERED IN A TABLE AS:
NAME1 (COLUMN 1)
NAME2 (COLUMN 2)
ADDRESS1 (COLUMN 3)
ADDRESS2 (COLUMN 4)
CITY (COLUMN 5)
STATE (COLUMN 6)
ZIP (COLUMN 7)
I USE THE FORMAT =[Forms]![INVOICE]![CONSCOMBO].[Column](1)
EACH FIELD HAS ITS OWN TEXT BOX EXCEPT CITY STATE AND ZIP ARE IN ONE BOX.
I CAN PUT EVERYTHING IN ONE BOX BUT I HAVE THE FOLLOWING PROBLEMS.
1) I WANT EACH FIELD TO DISPLAY ON A SEPARATE ROW.
2) IF NAME2 OR ADDRESS2 ARE NOT FILLED IN I DO NOT WANT TO HAVE A BLANK SPACE THERE.

ANY HELP IS APPRECIATED.

 

[jrbarnett]

Re No. 1, move them into separate fields.
Re no. 2, set the "Can Shrink" property of the textbox to true.

John

 

[gearhead03]

ARE YOU SAYING TO PUT THEM IN THE SAME TEXT BOX BUT SEPERATE FIELDS? I DO NOT UNDERSTAND.

 

[jrbarnett]

Yes - 3 separate fields, and use the Can Shrink property on them as well.

By the way, writing everything in upper case is considered shouting in email/web postings. It is also harder to read than lower case. In the future, please use ordinary sentence case.

John

 

[tzephon]

You could use iif() to remove unwanted lines.

=iif(isnull([ADDRESS1]),"", [ADDRESS1]& vbcr) & iif(isnull([ADDRESS2]),"", [ADDRESS2]&vbcr)

Or you could use a small vba function that would be easier to read in your text box to replace the iif statement to do the same thing.

private function TrimText( varText as variant) as variant
if not isnull(varText) then
TrimText = varText & vbcr
end if
end function

your text box would contain:
=TrimText([ADDRESS1] & TrimText([ADDRESS2])

etc, etc

 

[gearhead03]

Sorry bout the caps....it's a bad habit.
thanks for the help

 

[Steve101]

Create a new text field on the report to accomodate the full address, then concatenate the address line components using a combination of the [blue]&[/blue] and the [blue]+[/blue] string concatenation operators.

For example:
[tt]
FullAddress = AddressLine1 + vbcrlf & _
AddressLine2 + vbcrlf & _
City + vbcrlf & _
State + vbcrlf & _
Zip
[/tt]

This will produce the full address lines at the same time "squeezing out" any blank lines.

Here's how it works:

(a) the + operator returns a NULL string if either of the strings its operating on is NULL. Thus for example if AddressLine2 is null, then AddressLine2 + vbcrlf returns NULL, and no blank line is produced.

(b) The & operator returns a NULL only of both operands are null, otherwise if one is NULL, it returns the result of the non null one.

(c) This combination of the two concatenation operators allows the non null address components to be retained, without blank lines caused by the null address components.

Note: This technique will only work providing the address lines without a value are NULL as opposed to zero length strings (ie. "&quot

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gearhead03]

I am sure this is all just me doing something wrong. My computer HATES IT when i try using that vbcrlf or vbcr. it tries to name it as a field and then asks for a value. and of course then it doesn't put the seperate fields on their own lines.
HELP!!!
look it's not all caps

 

[Steve101]

I'm not sure how the vbcrlf constants are incorporated into the application. However in the absense of having them included, just add the following two lines above the code from my previous post:
[tt]
Dim vbcrlf as Variant
vbcrlf = Chr(13) & Chr(10)
[/tt]

that will declare a variable, and fulfill the same purpose, though the glabal declaration of the vb constants is much better practice, and perhaps someone else can add how they may be incorporated - may have something to do with the version of Access you are using.




[/tt]


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gearhead03]

I am using Access97 if that helps at all.

 

[Steve101]

k. may be wrong here, but from recollection, 97 may not (automatically) include the vb constants, so if you include the lines I added above to my previous post, it should work.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gearhead03]

Access keeps wanting me to put "" around the under bar _ is this correct?

 

[Steve101]

The underscore character is the line continuation character. It must be the last character on a line, and make sure that it is preceded by at least one space.

I have only included line continuations in my example for purposes of clarity. You could equally as well have specified the code in my example as:
[tt]
FullAddress = AddressLine1 + vbcrlf & AddressLine2 + vbcrlf & City + vbcrlf & State + vbcrlf & Zip
[/tt]


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gearhead03]

Note: This technique will only work providing the address lines without a value are NULL as opposed to zero length strings (ie. "&quot
I have finally gotten this code entered. However I still have the same problem. When i setup the address lines I did not enter any defaults. How can I tell for sure whether my lines a zls or NULL?

 

[scriverb]

Although Steve's method should work here is a really easy workaround that I have used in the past. Create all of your text boxes as you want them displayed in your report. Now modify the height property of Name2 and Address2 to 0". Also, modify the Can Grow property to Yes. This will make them look like a thin line. Now we want to tighten up the vertical spacing so that they are all touching. Select all of the controls and use the menu item Tools/vertical spacing/make equal. Then repeatedly use Tools/vertical spacing/decrease until everything is all tightened up. It will look as if the Name2 and Address2 are missing from the report. Now run the report. If there is data in either of these text boxes they will expand due to the Can Grow property being set to Yes and the line will be displayed. If no data(either null or empty String("") the text box will not expand.

Post back if you have any further questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Steve101]

Hi Bob,

My mouse co-ordination skills make the above difficult for me; too much shaking; only kidding. The method above really shows how different approaches can be applied to the same problem; nice one.

Another approach of course, is to "squeeze out" the blank lines using the replace command to replace the blank lines with an empty string;

for example, the following code will squeeze out an empty line providing two carriage return chr(13) characters appear together:

address = "Line1" & chr(13) & _
          "Line2" & chr(13) & _
           chr(13) & _
          "Line4"

address = replace(address, chr(13) & chr(13),chr(13))
address = replace(address, chr(13) & chr(13),chr(13))

The repeat of the last line is deliberate; to accomodate the situation where three chr(13)s occur together. A third or fourth repeat might be required for more than two in a row (in which case there's probably a problem with the address anyway).

Note that this approach will work providing no other white space characters are imposed between the two return characters; eg. space, tab etc; if so, some trimming conditioning might be advisable before hand. I think that the replace command only appeared from Access 2000, so this approach wont work pre this, unless you use your own replace functionality.

Finally, once refined, this code could be used as a nice little general function (called SqueezeAddress say), to be put into a general Utilities module, and used again in the future.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[scriverb]

Hi Steve. Good approach there. I like that. I will have to try that in A2000. Good to talk to you again. Been out doing my fall things for a few months and am now back getting into the swing of TT again. Looking for work after a year of retirement.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Steve101]

Hi Bob, yes nice to catch up with "old friends"; good luck with the work situation;
Regards,
Steve