Report text box pull one or many landowners/fields.

[sse1979]

In Access 2003, I created a database for tracking landowners. Created a
report that pulls landowner names with a text box (usingthe main data table,
data entered thru a form). Landowner names are: [Owner1FirstName],
[Owner1LastName], [Owner2FirstName], [Owner2LastName] all the way to 7
landowner first and last names, same format. If there are two landowners,
the text box on the report says =[Owner1FirstName] & " " & [Owner1LastName] &
" and " & [Owner2FirstName] & " " & [Owner2LastName]
With 3 landowners, I add & ", " & after the first owner and & ", and " &
after the 2nd owner. This format follows all the way to 7 landowners, with
the commas and the "and" in different locations. So I have 7 versions of one
report to account for the 7 ways of formatting the 7 landowners. Is there a
way using Iif (IsNull) Statements to account for this, and only have one
report that sticks the commas and the "and" where necessary depending on
whether the fields are blank? Something like: If Owner3 is null or blank,
use [Owner1] & " and " & [Owner2], if not then use [Owner1] & ", " & [Owner2]
& ", and " & [Owner3] ??? But all the way to 7? Or would some other type
of function or statement be more appropriate? Or use a query? Please help.
Thank you.

 

[Dr Steevil]

Without seeing how you have this set up, it sounds like you're going about it
a bit wrong. You should create 2 tables then have them link together in your
forms or reports.

Create a main table for your properties (include address information and
other pertinent info) and a second table for the landowners.) Something like
this:

tblProperties
idsPropertyID (primary key with AutoNumber data type)
chrAddress (Text Data Type)
other data fields as necessary

tblLandOwners
idsLandOwnerID (primary key with AutoNumber data type)
idsPropertyID (Number Data Type)
chrOwnerFirstName (Text Data Type)
chrOwnerLastName (Text Data Type)
other data fields as necessary

Using this type of set up will allow you to input as many landowners as you
need. You'll never be limited to a maximum.

Building your report is relatively easy.

Start with a new report based on the first table. Place your fields on the
report however you want them. Then you'll place a subreport on the form.
Look for a tool labled "Subform/Subreport". Click this then click on your
report where you want to place it. A wizard pop-up box will appear. Leave
"Use existing Tables and Queries" checked and click "Next". Select the main
table under "Tables/Queries" (in my example - tblProperties). Click the
"double-right" arrow button to select all fields and click "Next". You
should see the wizard suggest the fields it will use to link the subreport to
the main report. In this case it will be the idsPropertyID field. Click
Next or Finish and you're done!

Now on the subreport, click on both the chrOwnerFirstName and
chrOwnerLastName fields and set their Visible properties to No. Create an
unbound text box on the report. Set its Control Source as follows:

=[chrOwnerFirstName]&" "&[chrOwnerLastName]

This method doesn't really achieve what you're ultimately trying to get, but
you'll have a nice ordered list of your landowners. Unfortunately I don't
know exactly how to "concatenate" all of the names together like you're
asking (I'm not real good with VBA and I'm sure this is how you'll have to
go). I am curious myself how to do this as I will need to do something
similar as my own project develops.

Good luck!

In Access 2003, I created a database for tracking landowners. Created a
report that pulls landowner names with a text box (usingthe main data table,
data entered thru a form). Landowner names are: [Owner1FirstName],
[Owner1LastName], [Owner2FirstName], [Owner2LastName] all the way to 7
landowner first and last names, same format. If there are two landowners,
the text box on the report says =[Owner1FirstName] & " " & [Owner1LastName] &
" and " & [Owner2FirstName] & " " & [Owner2LastName]
With 3 landowners, I add & ", " & after the first owner and & ", and " &
after the 2nd owner. This format follows all the way to 7 landowners, with
the commas and the "and" in different locations. So I have 7 versions of one
report to account for the 7 ways of formatting the 7 landowners. Is there a
way using Iif (IsNull) Statements to account for this, and only have one
report that sticks the commas and the "and" where necessary depending on
whether the fields are blank? Something like: If Owner3 is null or blank,
use [Owner1] & " and " & [Owner2], if not then use [Owner1] & ", " & [Owner2]
& ", and " & [Owner3] ??? But all the way to 7? Or would some other type
of function or statement be more appropriate? Or use a query? Please help.
Thank you.

 

[Ken Sheridan]

What you want can be done by exploiting the fact that Nulls propagate
in arithmetical operations (Null + anything = Null). By using the +
arithmetical operator rather than the & concatenation operator you can
suppress the commas where the owner is Null. Each + operation has to
be enclosed in parentheses to force it to evaluate independently of
the concatenation operations. You'd then have to supplant the final
comma with an 'and'. So you'd really need to do it in a function
rather than a single expression.

However, while this is possible its only putting a sticking plaster
over a gaping wound, because the real problem is the flawed design of
the table. You should not have multiple columns for owners, but,
Access being a relational database system, a separate row per owner in
a related table. This related table would have a foreign key column,
e.g. LandUnitID which references the primary key column of the same
name of your current table. So the Owners table would have columns
LandUnitID , Fisrtname and LastName. If a land unit has 3 owners
there are 3 rows for it in Owners, each with the same LandUnitID
value; if it has 30 owners there are 30 rows for it in Owners, each
with the same LandUnitID value. This is how a relational database
works, and as you see gives you complete flexibility.

When it comes to reporting, then you could write a function which
concatenates the values from each row per LandUnitID in Owners.
Here's a simple one, which I've adapted to do what you want, which
does this for all addressees at one address:

Public Function GetAddressees(strAddress As String) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAddressees As String

strSQL = "SELECT FirstName & "" "" & LastName " & _
"AS FullName FROM Addresses " & _
"WHERE Address = """ & strAddress & _
""" ORDER BY LastName, FirstName"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strAddressees = strAddressees & ", " & _
.Fields("FullName")
.MoveNext
Loop
.Close
' remove leading comma and space
strAddressees = Mid$(strAddressees, 3)
End With

' replace final comma, if exists, with 'and'
If InStr(strAddressees, ",") > 0 Then
strAddressees = Left(strAddressees, InStrRev(strAddressees,
",") - 1) & _
" and" & Mid(strAddressees, InStrRev(strAddressees, ",") +
1)
End If

GetAddressees = strAddressees

End Function

A simpler option, though, would be to list the owners in separate
columns of an 'across then down' multi column subreport. You'll find
an example at:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271

The file also includes a single report which does the same thing by
modifying the layout in code at runtime. That was only produced in
answer to a question by a reader of a magazine column written by a
contact of mine to show that it could be done, not as a serious
solution. A subreport is far easier.

Ken Sheridan
Stafford, England

In Access 2003, I created a database for tracking landowners. Created a
report that pulls landowner names with a text box (usingthe main data table,
data entered thru a form). Landowner names are: [Owner1FirstName],
[Owner1LastName], [Owner2FirstName], [Owner2LastName] all the way to 7
landowner first and last names, same format. If there are two landowners,
the text box on the report says =[Owner1FirstName] & " " & [Owner1LastName] &
" and " & [Owner2FirstName] & " " & [Owner2LastName]
With 3 landowners, I add & ", " & after the first owner and & ", and " &
after the 2nd owner. This format follows all the way to 7 landowners, with
the commas and the "and" in different locations. So I have 7 versions of one
report to account for the 7 ways of formatting the 7 landowners. Is there a
way using Iif (IsNull) Statements to account for this, and only have one
report that sticks the commas and the "and" where necessary depending on
whether the fields are blank? Something like: If Owner3 is null or blank,
use [Owner1] & " and " & [Owner2], if not then use [Owner1] & ", " & [Owner2]
& ", and " & [Owner3] ??? But all the way to 7? Or would some other type
of function or statement be more appropriate? Or use a query? Please help.
Thank you.

 

[Amy E. Baggott]

Putting your data in two related tables also makes it easy enough to create a
single field with all the owners in it if you need it for a Word merge or
something like that using a simple code loop. I have a VBA process that does
that with booth numbers for my directory listings and invoice cover letters.

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

Without seeing how you have this set up, it sounds like you're going about it
a bit wrong. You should create 2 tables then have them link together in your
forms or reports.

Create a main table for your properties (include address information and
other pertinent info) and a second table for the landowners.) Something like
this:

tblProperties
idsPropertyID (primary key with AutoNumber data type)
chrAddress (Text Data Type)
other data fields as necessary

tblLandOwners
idsLandOwnerID (primary key with AutoNumber data type)
idsPropertyID (Number Data Type)
chrOwnerFirstName (Text Data Type)
chrOwnerLastName (Text Data Type)
other data fields as necessary

Using this type of set up will allow you to input as many landowners as you
need. You'll never be limited to a maximum.

Building your report is relatively easy.

Start with a new report based on the first table. Place your fields on the
report however you want them. Then you'll place a subreport on the form.
Look for a tool labled "Subform/Subreport". Click this then click on your
report where you want to place it. A wizard pop-up box will appear. Leave
"Use existing Tables and Queries" checked and click "Next". Select the main
table under "Tables/Queries" (in my example - tblProperties). Click the
"double-right" arrow button to select all fields and click "Next". You
should see the wizard suggest the fields it will use to link the subreport to
the main report. In this case it will be the idsPropertyID field. Click
Next or Finish and you're done!

Now on the subreport, click on both the chrOwnerFirstName and
chrOwnerLastName fields and set their Visible properties to No. Create an
unbound text box on the report. Set its Control Source as follows:

=[chrOwnerFirstName]&" "&[chrOwnerLastName]

This method doesn't really achieve what you're ultimately trying to get, but
you'll have a nice ordered list of your landowners. Unfortunately I don't
know exactly how to "concatenate" all of the names together like you're
asking (I'm not real good with VBA and I'm sure this is how you'll have to
go). I am curious myself how to do this as I will need to do something
similar as my own project develops.

Good luck!

In Access 2003, I created a database for tracking landowners. Created a
report that pulls landowner names with a text box (usingthe main data table,
data entered thru a form). Landowner names are: [Owner1FirstName],
[Owner1LastName], [Owner2FirstName], [Owner2LastName] all the way to 7
landowner first and last names, same format. If there are two landowners,
the text box on the report says =[Owner1FirstName] & " " & [Owner1LastName] &
" and " & [Owner2FirstName] & " " & [Owner2LastName]
With 3 landowners, I add & ", " & after the first owner and & ", and " &
after the 2nd owner. This format follows all the way to 7 landowners, with
the commas and the "and" in different locations. So I have 7 versions of one
report to account for the 7 ways of formatting the 7 landowners. Is there a
way using Iif (IsNull) Statements to account for this, and only have one
report that sticks the commas and the "and" where necessary depending on
whether the fields are blank? Something like: If Owner3 is null or blank,
use [Owner1] & " and " & [Owner2], if not then use [Owner1] & ", " & [Owner2]
& ", and " & [Owner3] ??? But all the way to 7? Or would some other type
of function or statement be more appropriate? Or use a query? Please help.
Thank you.