Stripping Blank Lines from text box

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi
I have a text box on a form called 'disAddress' with 'Enter Key Behavior'
set to 'New Line in Field'
I have 9 variables D(0) to D(8) that contains lines of addresses
for exmple D(0) could equal "123 Freet Street" D(1) could equal "Near
Dartford" and D(3) could equal "" and D(4) could equal "Australia" etc.

I am tryng to display the address in a text box with no blank lines taking
the information from the variables.

D(0) = "25 Albert Road"
D (1) = ""
D(2) ="Wilmington"

I dont want it to show
25 Albert Road

Wilmington

I want it to show
25 Albert Road
Wilmington

So I need somehow to create a routine that strips blank lines or ignors the
variables with blank lines and is able to fill the text box with the
information from the variables.

Any help most apreciated

Steve - From a land down under or ontop if you are in outa space
 
Hi
I have a text box on a form called 'disAddress' with 'Enter Key Behavior'
set to 'New Line in Field'
I have 9 variables D(0) to D(8) that contains lines of addresses
for exmple D(0) could equal "123 Freet Street" D(1) could equal "Near
Dartford" and D(3) could equal "" and D(4) could equal "Australia" etc.

I am tryng to display the address in a text box with no blank lines taking
the information from the variables.

D(0) = "25 Albert Road"
D (1) = ""
D(2) ="Wilmington"

I dont want it to show
25 Albert Road

Wilmington

I want it to show
25 Albert Road
Wilmington

So I need somehow to create a routine that strips blank lines or ignors the
variables with blank lines and is able to fill the text box with the
information from the variables.

Any help most apreciated

Steve - From a land down under or ontop if you are in outa space
Click to expand...

I've gotta run but the Join function sounds like it could help you
here - check the help files and let me know if you need, er, help. It
will join an array together using user-defined rules (to an extent).

--James
 
Hi
I have a text box on a form called 'disAddress' with 'Enter Key Behavior'
set to 'New Line in Field'
I have 9 variables D(0) to D(8) that contains lines of addresses
for exmple D(0) could equal "123 Freet Street" D(1) could equal "Near
Dartford" and D(3) could equal "" and D(4) could equal "Australia" etc.

I am tryng to display the address in a text box with no blank lines taking
the information from the variables.

D(0) = "25 Albert Road"
D (1) = ""
D(2) ="Wilmington"

I dont want it to show
25 Albert Road

Wilmington

I want it to show
25 Albert Road
Wilmington

So I need somehow to create a routine that strips blank lines or ignors the
variables with blank lines and is able to fill the text box with the
information from the variables.
Click to expand...

Does this array originate from fields in a table? If so you could avoid the
array and not need ANY code at all.

If you do have an array, you could use code like

Dim strAddr As String
Dim i As Integer
For i = 0 to 8
If D(i) & "" > "" Then
strAddr = strAddr & D(i) & vbCrLf
End If
Next i

Then set the Control Source of the textbox to

Chr(34) & strAddr & Chr(34)

Chr(34) is the doublequote character ", so the textbox's control source will
be a literal string.

John W. Vinson [MVP]
 
Thanks John
I tried your code and it looks very sound to me but it does not work here
for some reason. Yes the array does come from a table of sorts it actualy
comes from a combo box that has a query that gets information from a read
only linked comma seperated text table.

Here is the lines of code that dont want to work for me at present.
the cmbCustomer is a combo box with a select statement.
the results I get when I debug is as follows
D(0) = "Anything"
D(1) ="Anything"
D(2) = "Anything"
D(3 = "Anything"
D(4) ="Anything"
D(5) = "Anything"
D(6) = "Anything"
D(7) ="Anything"
D(8) = "Anything"

: strAddr : "Bexley Enterprises 94018055 41 Ellendale Drive line 1 Line 2
Line3 Line4 Heathridge WA "

and I get the result I get in the text box is #Name?

THE CODE

Private Sub cmbCustomer_AfterUpdate()
Dim D(8) As String
Dim strAddr As String
Dim i As Integer

For i = 0 To 8
D(i) = Me!cmbCustomer.Column(i)
Next i
For i = 0 To 8
If D(i) & "" > "" Then
strAddr = strAddr & D(i) & vbCrLf
Me!dispAddress.ControlSource = Chr(34) & strAddr & Chr(34)
End If
Next i

'Then set the Control Source of the textbox to

Forms!frmSales!dispAddress.ControlSource = Chr(34) & strAddr & Chr(34)

'Chr(34) is the doublequote character ", so the textbox's control source will
'be a literal string.
End Sub
 
John
Disregard the last Post sorry I left a line of code in when testing.
It looks like this now but still not working for me yet.

Thanks John
I tried your code and it looks very sound to me but it does not work here
for some reason. Yes the array does come from a table of sorts it actualy
comes from a combo box that has a query that gets information from a read
only linked comma seperated text table.

Here is the lines of code that dont want to work for me at present.
the cmbCustomer is a combo box with a select statement.
the results I get when I debug is as follows
D(0) = "Anything"
D(1) ="Anything"
D(2) = "Anything"
D(3 = "Anything"
D(4) ="Anything"
D(5) = "Anything"
D(6) = "Anything"
D(7) ="Anything"
D(8) = "Anything"

strAddr = "Bexley Enterprises 94018055 41 Ellendale Drive line 1 Line 2
Line3 Line4 Heathridge WA "

and I get this result in the text box #Name?

THE CODE

Private Sub cmbCustomer_AfterUpdate()
Dim D(8) As String
Dim strAddr As String
Dim i As Integer

For i = 0 To 8
D(i) = Me!cmbCustomer.Column(i)
Next i
For i = 0 To 8
If D(i) & "" > "" Then
strAddr = strAddr & D(i) & vbCrLf
End If
Next i


Forms!frmSales!dispAddress.ControlSource = Chr(34) & strAddr & Chr(34)

End Sub
 
OK John its working now and thank you so much for youir help
somtimes its just looking you straight in the face but cant see it.
the = "=" made all the diffrence in the line of code below and now its working
Thank you again
I am intreeged to know how you would have done it with no code direct from
table.
Hmm if ya have time I would love to know.
Steve


Forms!frmSales!dispAddress.ControlSource = "=" & Chr(34) & strAddr & Chr(34)
 
I am intreeged to know how you would have done it with no code direct from
table.
Click to expand...

A couple of ways. One would be to base your Report or Form directly on the
linked textfile "table", and set the Control Source of a textbox to an
expression like

=([Field1] + Chr(13) + Chr(10)) & ([Field2] + Chr(13) + Chr(10)) & ([Field3] +
Chr(13) + Chr(10)) & .... <and so on through all the fields>

The & operator and the + operator both concatenate strings - but handle NULL
values differently. [Field1] + <some string, a carriage return/linefeed pair
in this case> will return the concatenation if there is anything in Field1,
but will return NULL - no extra CrLf - if Field1 is NULL. The & operator will
treat NULL values as an empty string and build up the concatenation.

Or, you can use DLookUp to look up the same expression based on a criterion,
if that's more appropriate in your case.

John W. Vinson [MVP]
 
Back
Top