Address1, Address2 and Address3

I have a requirement to have a mailing label the can have up to 3 Address
fields
Homer Simpson
Address Line 1
Address Line 2
Address Line 3
Springfield, IL 999999
If 2 an 3 are not used I would like the City State and Zip to move up under
Address line 1, right now there are blank lines between Address 1 and
City/State.
I'm using a Table RDL element to display the fields.
JeffExcellent idea! I tried that on one of my reports and it seems to work
well.
--
Keith
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Hey Sunny,
Your solution Hides the row (makes invisible) but the rows don't get removed
to bring Address 1 and Address 3, if there is no value in Address 2.
Can you suggest a solution?
It seam like we need a property to "align" the rows "Bring Together"
Jeff
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Keith, what other properties (expressions) did you set for the Fields?
Jeff
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Excellent idea! I tried that on one of my reports and it seems to work
> well.
> --
> Keith
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > i am suggesting just after reading your question
> >
> > if each address is on a seperate row in the table...why don't u HIDE the
> > rows Address2 and Address3 depending on wheather they are nothing or
> empty as
> > Hidden = iif(isnothing(Address2),True,False)
> >
> > let us know if this works...
> >
> > "Jeff" wrote:
> >
> > > I have a requirement to have a mailing label the can have up to 3
> Address
> > > fields
> > >
> > > Homer Simpson
> > > Address Line 1
> > > Address Line 2
> > > Address Line 3
> > > Springfield, IL 999999
> > >
> > > If 2 an 3 are not used I would like the City State and Zip to move up
> under
> > > Address line 1, right now there are blank lines between Address 1 and
> > > City/State.
> > >
> > > I'm using a Table RDL element to display the fields.
> > >
> > > Jeff
>|||Got it!.. need to set the expression on the Row, I was doing it on the
Field/Cell...
Works perfect...
Thanks guys
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:23A578CF-E937-4A0B-BDAA-CBA2468F24C8@.microsoft.com...
> I have a requirement to have a mailing label the can have up to 3 Address
> fields
> Homer Simpson
> Address Line 1
> Address Line 2
> Address Line 3
> Springfield, IL 999999
> If 2 an 3 are not used I would like the City State and Zip to move up
under
> Address line 1, right now there are blank lines between Address 1 and
> City/State.
> I'm using a Table RDL element to display the fields.
> Jeff|||nothing.
I selected the row (click on the grey box outside [to the left] of the
table) and then navigated to the properties window. Expand visibility. I
added the following expression under Hidden:
=iif(isnothing(Fields!AddlInfo.Value),True,False)
(My column is called AddlInfo)
--
Keith
"Jeff" <ernzddy@.msn.com> wrote in message
news:ukGHiV9fEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Keith, what other properties (expressions) did you set for the Fields?
> Jeff
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> > Excellent idea! I tried that on one of my reports and it seems to work
> > well.
> >
> > --
> > Keith
> >
> >
> > "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> > news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > > i am suggesting just after reading your question
> > >
> > > if each address is on a seperate row in the table...why don't u HIDE
the
> > > rows Address2 and Address3 depending on wheather they are nothing or
> > empty as
> > > Hidden = iif(isnothing(Address2),True,False)
> > >
> > > let us know if this works...
> > >
> > > "Jeff" wrote:
> > >
> > > > I have a requirement to have a mailing label the can have up to 3
> > Address
> > > > fields
> > > >
> > > > Homer Simpson
> > > > Address Line 1
> > > > Address Line 2
> > > > Address Line 3
> > > > Springfield, IL 999999
> > > >
> > > > If 2 an 3 are not used I would like the City State and Zip to move
up
> > under
> > > > Address line 1, right now there are blank lines between Address 1
and
> > > > City/State.
> > > >
> > > > I'm using a Table RDL element to display the fields.
> > > >
> > > > Jeff
> >
>

Address table structure

Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql

Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill