line break in text field 2

[pandapark]

hi

I have a report with a text field which concatenates other text boxes e.g. =[Text1] + " " +[Text2]

I'd like to have a line break between Text1 and Text2, something like
=[Text1] & vbcr & [Text2] but no joy

any ideas much appreciated
gwilym

 

[LittleSmudge]

You need the VB value vbCrLf because you have to do a Carraige Return AND a Line Feed

Only those of us who remember 180 char line printers and TTY machines really understand why !

So
=[Text1] & vbCrLf & [Text2]

WILL work for you.


( For some strange reason, in a MsgBox you CAN get away with vbLf only - but vbCrLf still works as well )




'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Aubs010]

In a VB MsgBox you can use Chr(13) as a line feed

ok, the help file says different!!!

[tt]Numbers from 0 – 31 are the same as standard, nonprintable ASCII codes. For example, Chr(10) returns a linefeed character. The normal range for charcode is 0 – 255. However, on DBCS systems, the actual range for charcode is -32768 to 65535.[/tt]


Aubs

 

[pandapark]

thanks
I have another problem now

I've got 5 text fields that I am concatenating - using the above method, if the first 4 text fields are null then a big vertical gap shows on the report - i tried saying, for each text field, =IIf(Not IsNull([Text1]),[Text1] & Chr(13) & Chr(10),"&quot and then say [Text1]+[Text2] etc - still leaves the vertical gap though?

 

[Aubs010]

Something I used VERY recently, in fact this week! (took me ages to get it to work!!!)

="Text goes here " & [Site Address1] & IIf([Site Address1] Is Not Null,IIf([Site Address2] Is Null And [Site Address3] Is Null And [Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", &quot,"&quot & [Site Address2] & IIf([Site Address2] Is Not Null,IIf([Site Address3] Is Null And [Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", &quot,"&quot & [Site Address3] & IIf([Site Address3] Is Not Null,IIf([Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", &quot,"&quot & [Site Town/City] & IIf([Site Town/City] Is Not Null,IIf([Site County] Is Null And [Site PostCode] Is Null,".",", &quot,"&quot & [Site County] & IIf([Site County] Is Not Null,IIf([Site PostCode] Is Null,".",", &quot,"&quot & [Site PostCode] & IIf([Site PostCode] Is Not Null,".","&quot & " More text goes here."

This way, I wanted it to give me text in this format:

House, Lane, Street, City, County, PostCode.
Lane, Street, City, County, PostCode.
House, Street, City, County, PostCode.
House, Street, City, County.
Street, City, County, PostCode.

etc so if one was missing, it wouldn't try and place it. you could try doing the same only with chr(13) & Chr(10) in place of the "," or "."



Aubs

 

[Aubs010]

Last post... I think this should be EXACTLY what you are looking for... Just copy and paste the code!:


="Bleh" & Chr(13) & Chr(10) & [Text1] & IIf([Text1] Is Not Null,IIf([Text2] Is Null And [Text3] Is Null And [Text4] Is Null And [Text5] Is Null,"",Chr(13) & Chr(10)),"&quot & [Text2] & IIf([Text2] Is Not Null,IIf([Text3] Is Null And [Text4] Is Null And [Text5] Is Null,"",Chr(13) & Chr(10)),"&quot & [Text3] & IIf([Text3] Is Not Null,IIf([Text4] Is Null And [Text5] Is Null,"",Chr(13) & Chr(10)),"&quot & [Text4] & IIf([Text4] Is Not Null,IIf([Text5] Is Null,"",Chr(13) & Chr(10)),"&quot & [Text5] & Chr(13) & Chr(10) & "Bleh."

| When you have | You should get |
|_______________|________________|
| Text1= Hello  | Bleh           |
| Text2= My     | Hello          |
| Text3= Name   | My             |
| Text4= Is     | Name           |
| Text5= Aubs   | Is             |
|               | Aubs           |
|               | Bleh           |
|---------------|----------------|
| Text1=        | Bleh           |
| Text2= My     | My             |
| Text3= Name   | Name           |
| Text4= Is     | IS             |
| Text5= Aubs   | Aubs           |
|               | Bleh           |
|---------------|----------------|
| Text1= Hello  | Bleh           |
| Text2= My     | Hello          |
| Text3=        | My             |
| Text4=        | Aubs           |
| Text5= Aubs   | Bleh           |
|---------------|----------------|
| Text1= Hello  | Bleh           |
| Text2=        | Hello          |
| Text3=        | Aubs           |
| Text4=        | Bleh           |
| Text5= Aubs   |                |
|_______________|________________|

Hope this is what you are looking for and it does the job.


Aubs

 

[LittleSmudge]

Copy the following function into a Global module and it will fix all of your problems

Public Function fnStringCombiner(strResult As Variant, strSuffix As Variant _
                               , strSeparator As String) As String
'# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #'
' This function is designed to support the accumilation of a growing string of text '
' data where each item is progressively added with the separator string between each. '
' However, if either of the text strings are blank then the separator text is NOT added. '
' This avoids double separators ( blank lines or over large spaces ) in the result.     '
' In addition, the use of Varient types allows the function to cope with NULL inputs   '
' Yet NULLs are NOT propogated through the function.                                  '
' If both inputs are Null the output = ""                                            '
'# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #'
If Len(Nz(strResult, "")) = 0 Then            ' Result is blank
    fnStringCombiner = Nz(strSuffix, "")      ' so just return strSuffix
ElseIf Len(Nz(strSuffix, "")) = 0 Then        ' Suffix is blank
    fnStringCombiner = Nz(strResult, "")      ' so just return strReturn
Else
    fnStringCombiner = strResult & strSeparator & strSuffix     ' Do the usual combination job
End If
End Function

Usage Example


strResult = "Hello"
strSuffix = "Fred"
strSeparator = vbCrLf ' Or could be " " etc.
strResult = fnStringCombiner(strResult, strSuffix, strSeparator)



'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[pandapark]

thnaks to everybody - this will come in really useful for reports

thanks