Conditional Printing of Fields

[Auguy]

This is in RS 2000. I have an invoice report with the customer's address on the report. The fields are customer name, address1, address2, city, state, zip. The address1, address2 and a textbox for the city/state/zip are located vertically one below the other. I would like the address2 field to automatically float up to the space allocated for address1 if address1 does not have any text in it. I Also want the city/state/zip field to move up if address2 has no text, etc. Is there a float or don't print if empty on the textboxes? Any ideas how to do this?

Auguy

 

[xlbo]

AFAIK, there is no relative positioning capabilities within RS

What I have done for addresses in the past is to create a function that tests the relevant fields and concatenates them together to form a nicely presented address. Then, just use 1 text box, sized appropriately to display e.g.

Public Shared Function GetFullAddress(ByVal Address1 As String, ByVal Address2 As String, ByVal Address3 As String, ByVal TownCity As String, ByVal PostCode As String) As String

        'Trim input data and declare variables
        Dim RetVal As System.Text.StringBuilder = New System.Text.StringBuilder
        Address1 = Address1.Trim()
        Address2 = Address2.Trim()
        Address3 = Address3.Trim()
        TownCity = TownCity.Trim()
        PostCode = PostCode.Trim()

        'Build Response
        If (Address1.Length > 0) and (Not(IsNothing(Address1))) Then
            RetVal.Append(Address1)
            RetVal.Append(vbCrLf)
        End If
        If (Address2.Length > 0) and (Not(IsNothing(Address2))) Then
            RetVal.Append(Address2)
            RetVal.Append(vbCrLf)
        End If
        'If (Address3.Length > 0) and (Not(IsNothing(Address3)))Then
        '    RetVal.Append(Address3)
        '    RetVal.Append(vbCrLf)
        'End If
        If (TownCity.Length > 0) and (Not(IsNothing(TownCity))) Then
            RetVal.Append(TownCity)
            RetVal.Append(vbCrLf)
        End If
        If (PostCode.Length > 0) and (Not(IsNothing(PostCode))) Then
            RetVal.Append(PostCode)
            RetVal.Append(vbCrLf)
        End If

	Return RetVal.ToString()

    End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

And if you don't want to concate it in SQL, you can do it with the Expressions property of the Text Box itself.

=Address1 & " " & Address2

That gives you a space between the addresses. I'm not sure how you would do a carriage return there if you wanted Address1 & Address2 on separate lines, but if you want to use one, you could then use an IIF statement similar to:

=IIF(isnothing(Address1),Address2,Address1 & " " & Address2)

You'd have to do something with the carriage return in the Concate part of the IIF statement around where the blank space is inserted, of course.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[xlbo]

Catadmin - strangely enough, you can use vbcrlf to create a line break in the expression builder

=Address1 & vbcrlf & Address2

will create a 2 line textbox

I just used code because I didn't want to have to deal with lots of nested IFs !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Thanks to all. I used Geoff's suggestion and modified his function to work with my fields. It worked perfectly. The only problem I had was when I added a sixth parameter, it didn't seem to like having more than 5. Is there some limitation on the number of parameters in the code section of a report? I got around it by combining a field and the GetFullAddress function with "vbcrlf".

Auguy

 

[Catadmin]

I don't know of any limit myself, but that doesn't mean there isn't any. Are you using a TextBox?

You could try changing the Textbox to a Rectangle, but I'm not sure if you can put a value into a Rectangle, let alone an expression concating a value.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[xlbo]

Don't know of any limit either - would be surprised if there was a 5 param limit....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Apologies to everyone. After reading Catadmin's and Geoff's
response, I did some more research and I found the problem (me). I had the same function farther down the report that wasn't visible unless I scrolled down and I had not changed the function call there to send the correct number of parameters. It now works with my six parameters. Yes, this is a textbox inside of a rectangle.

Auguy

 

[Catadmin]

Glad you were able to get it to work. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"