Dynamic Address lines 1

[sirace]

I have a database of clients in which there are 6 fields set aside for address lines. There is also a field called "namelines" that has a value of 1 to 6. This value shows how many fields are actual names of clients and not address portions. Is there a way to have my report show only the name lines based on the field "namelines"?

e.g.

address1 John Doe
address2 Jane Doe
address3 123 Main Street
address4 Cleveland, OH 44122
address5
address6
namelines 2

address1 James Smith
address2 456 Main Street
address3 Cleveland, OH 44122
address4
address5
address6
namelines 1

address1 Jack Smith Cust for
address2 01/01
address3 Jay Smith **DECEASED**
address4 UND MI UNIF GIFTS TO MIN ACT
address5 2614 Main Street
address6 Cleveland, OH 44122
namelines 4

So the report would show the following information:

John Doe
Jane Doe

John Smith

Jack Smith Cust for
01/01
Jay Smith **DECEASED**
UND MI UNIF GIFTS TO MIN ACT

for each one of those entries. At the moment I'm listing all address lines, but I'm winding up with a lot of blank space and I feel that if I can use the namelines data, then I should definitely try.

 

[JonFer]

There are a couple of options depending on how you want to display the result on the report and what other fields are there. Can you show a few lines of the desired report output?

 

[sirace]

Sure, this is how I want it to look.

Account No.             Client Name
----------------------------------------------------
00000001                John Doe
                        Jane Doe

00000002                James Smith

00000003                Jack Smith Cust for
                        01/01
                        Jay Smith **DECEASED**
                        UND MI UNIF GIFTS TO MIN ACT
----------------------------------------------------

This is how it looks currently:

Account No.             Client Name
----------------------------------------------------
00000001                John Doe
                        Jane Doe
                        123 Main Street
                        Cleveland, OH 44122



00000002                James Smith
                        456 Main Street
                        Cleveland, OH 44122




00000003                Jack Smith Cust for
                        01/01
                        Jay Smith **DECEASED**
                        UND MI UNIF GIFTS TO MIN ACT
                        2614 Main Street
                        Cleveland, OH 44122

----------------------------------------------------

So as you can see, the way it is now, uses much more page space.

 

[JonFer]

One option is to write a Union query with six selects. Each would bring in the Account number and one of the address fields IF it was not null.

e.g. Select Account, Address1 as Address from TableName Where Address1 Is Not Null
Union All
Select Account, Address2 from TableName where Address2 Is Not Null
Union All ...

Another option is to make a control on the report have all 6 address fields but insert CR/LF characters where needed. Set the control's "CanGrow" property to yes and set it's height in design view to be for one row. The source for the control would be:

Address: Address1 &
IIf(IsNull(Address2),"",Chr$(13) & Chr$(10) & Address2) &
IIf(IsNull(Address3),...

Do the same for each of the other Address fields.

 

[BNPMike]

Just a word of warning here. The relational model assumes you are using relations. What you have got is data structures which are not relational. You are using a field to dynamically change the meaning of other fields. Each record is different. A relation is a table where each record has values in a particular field drawn from the same 'domain' ie set of things.

The result of this is you cannot use the full power of SQL. I would advise you to change your model. Try and break up th data into entities rather than slots to stuff miscellaneous data into.

 

[sirace]

It would be too difficult to break up this data as it comes from another source in this format. I would love to break down those address lines, etc. But it would be too much work for me. This database of 90000 records is only a small part of a bigger database of 600000+ entries.

 

[sirace]

Ok, I've been trying your suggestions JonFer, the second one in particular. It seems to me it should work great, but when I try and use it, I get the following error message:

" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Here's what I have in my control:

address: address_1 & (IIf(IsNull(address_2),"",Chr$(13) & Chr$(10) & address_2) & (IIf(IsNull(address_3),"",Chr$(13) & Chr$(10) & address_3) & (IIf(IsNull(address_4),"",Chr$(13) & Chr$(10) & address_4) & (IIf(IsNull(address_5),"",Chr$(13) & Chr$(10) & address_5) & (IIf(IsNull(address_6),"",Chr$(13) & Chr$(10) & address_6)

 

[JonFer]

You have some extra parentheses. This works for me.

Address: [address_1] & IIf(IsNull([address_2]),"",Chr$(13) & Chr$(10) & [address_2]) & IIf(IsNull([address_3]),"",Chr$(13) & Chr$(10) & [address_3]) & IIf(IsNull([address_4]),"",Chr$(13) & Chr$(10) & [address_4]) & IIf(IsNull([address_5]),"",Chr$(13) & Chr$(10) & [address_5]) & IIf(IsNull([address_6]),"",Chr$(13) & Chr$(10) & [address_6])

 

[sirace]

Ok, modified my statement, now getting a syntax error. Am I supposed to put this in a query, then access that query through the report? I tried putting "Address: [address_1] & [address_2]" into the control and still got "syntax error in expression". Anyway, you deserve a star for your help so far. This should work, I understand the logic and concept of it all, just wondering why I'm getting the errors.

 

[sirace]

Just an update, I've put the expression into a query and then through that into my report and changed CanGrow to yes and voila! it works! Thanks again!