Actually I would write the function like:
Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, title, company, Address1, Address2, City, State, Zip)
Dim X As Integer, strLine As String
For X = 0 To UBound(arrLines)
If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
Select Case X
Case 1, 6
strLine = strLine & ", " & arrLines(X)
Case 7
strLine = strLine & " " & arrLines(X)
Case Else
strLine = strLine & vbCrLf & arrLines(X)
End Select
End If
Next
' remove leading carriage return/line feed
CanShrinkLines = Mid(strLine, 3)
End Function
Use it in your query or control source like:
=CanShrinkLines([Cname],[title],[coName],[streetline1],[streetline2],[city],[state],[zip])
I'm not sure why you are check for '0' or needing to trim the values.
Duane Hookom, MVP
MS Access
From: MS_Access_Professionals@yahoogroups.com
Thank you very much Duane and Mr. Viesca
I have modified the code in the label's control source like what Mr. Viesca recommended - use a Null and the "&". It is now working very good.
I've also tried out the function in the thread which Duane Hookom mentioned. I first placed this code
=CanShrinkLines(IIf([Cname]='0',Null,Trim([Cname])),IIf([ctitle]='0',Null,Trim(c[title])),IIf([coname]='0',Null,Trim([coName])),IIf([streetline1]='0',Null,Trim([streetline1])),IIf([streetline2]='0',Null,Trim([streetline2])),IIf([city]='0',Null,Trim([city])),IIf([state]='0',Null,Trim([state])),IIf([zip]='0',Null,Trim([zip])))
in the label's control source, I then added an If statement to the function (see below), so the City State and Zip will be printed on the same line instead of dropdown, printed on a separate line.That seems to be working fine, however I am wondering if this is the correct way to do or there's an appropriate, better way to do?
Thanks
Phucon
Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, Address1, Address2, City, State, Zip)
Dim X As Integer, strLine As String
For X = 0 To UBound(arrLines)
If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
If X = 6 Or X = 7 Then
strLine = strLine & "" & arrLines(X)
Else
strLine = strLine & vbCrLf & arrLines(X)
End If
End If
Next
' remove leading carriage return/line feed
CanShrinkLines = Mid(strLine, 3)
End Function
Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, title, company, Address1, Address2, City, State, Zip)
Dim X As Integer, strLine As String
For X = 0 To UBound(arrLines)
If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
Select Case X
Case 1, 6
strLine = strLine & ", " & arrLines(X)
Case 7
strLine = strLine & " " & arrLines(X)
Case Else
strLine = strLine & vbCrLf & arrLines(X)
End Select
End If
Next
' remove leading carriage return/line feed
CanShrinkLines = Mid(strLine, 3)
End Function
Use it in your query or control source like:
=CanShrinkLines([Cname],[title],[coName],[streetline1],[streetline2],[city],[state],[zip])
I'm not sure why you are check for '0' or needing to trim the values.
Duane Hookom, MVP
MS Access
From: MS_Access_Professionals@yahoogroups.com
Thank you very much Duane and Mr. Viesca
I have modified the code in the label's control source like what Mr. Viesca recommended - use a Null and the "&". It is now working very good.
I've also tried out the function in the thread which Duane Hookom mentioned. I first placed this code
=CanShrinkLines(IIf([Cname]='0',Null,Trim([Cname])),IIf([ctitle]='0',Null,Trim(c[title])),IIf([coname]='0',Null,Trim([coName])),IIf([streetline1]='0',Null,Trim([streetline1])),IIf([streetline2]='0',Null,Trim([streetline2])),IIf([city]='0',Null,Trim([city])),IIf([state]='0',Null,Trim([state])),IIf([zip]='0',Null,Trim([zip])))
in the label's control source, I then added an If statement to the function (see below), so the City State and Zip will be printed on the same line instead of dropdown, printed on a separate line.That seems to be working fine, however I am wondering if this is the correct way to do or there's an appropriate, better way to do?
Thanks
Phucon
Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, Address1, Address2, City, State, Zip)
Dim X As Integer, strLine As String
For X = 0 To UBound(arrLines)
If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
If X = 6 Or X = 7 Then
strLine = strLine & "" & arrLines(X)
Else
strLine = strLine & vbCrLf & arrLines(X)
End If
End If
Next
' remove leading carriage return/line feed
CanShrinkLines = Mid(strLine, 3)
End Function
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar