Category Heading for Each Section

[zmerz]

I have a report based on a subquery. In the report I would like to break up
the report when the first letter of the last name changes. I am unable to
group on the last name because of the subquery. I have a subquery to combine
the first names of the households. Does anyone know how I can write my
report? This is what I'm trying to accomplish:

S

Smith, John & Jane
............................................................281-555-1212
1313 Mockingbird Lane

Thanks!

 

[Evi]

In the Sorting Grouping box, type
=Left([LastName],1)
Choose Yes next to Group Header
Evi

 

[zmerz]

Hi Evi,

Thanks for the reply. I am unable to use a group header with a subquery;
the report won't allow it. Any other ideas?

In the Sorting Grouping box, type
=Left([LastName],1)
Choose Yes next to Group Header
Evi

I have a report based on a subquery. In the report I would like to break up
the report when the first letter of the last name changes. I am unable to
group on the last name because of the subquery. I have a subquery to combine
the first names of the households. Does anyone know how I can write my
report? This is what I'm trying to accomplish:

S

Smith, John & Jane
...........................................................281-555-1212
1313 Mockingbird Lane

Thanks!

 

[Evi]

Sorry zmerz, I never even knew it wasn't possible until now (I've just tried
it out).

Here is one solution which you may be able to improve upon.:

In a new Module in your database paste the following:

Option Compare Database
Option Explicit
Dim GlobalFlag As Variant

Function SetFlag(x As Variant)
If GlobalFlag <> x Then
SetFlag = 1
Else
SetFlag = 0
End If

GlobalFlag = x
End Function

Save and close the Module
Add a text box to your report, in the position where you want the text to
break up.
In Properties name it txtLetter and set Visible to No


In the box type
=Left([Surname],1)
Add another text box, name it txtChange
Make it invisible also
In it, type
=SetFlag(txtLetter)

Set the Can Grow/Can Shrink Property to Yes for the Details section of your
report and for all the controls in it, including these new ones

Click on the Detail section of your report
In Properties click on Events and next to On Format, open up a code page.
Just above End Sub paste


If Me.txtChange = 1 Then
Me.txtLetter.Visible = True
Else
Me.txtLetter.Visible = False
End If

Evi

Hi Evi,

Thanks for the reply. I am unable to use a group header with a subquery;
the report won't allow it. Any other ideas?

In the Sorting Grouping box, type
=Left([LastName],1)
Choose Yes next to Group Header
Evi

I have a report based on a subquery. In the report I would like to

break
up

the report when the first letter of the last name changes. I am unable to
group on the last name because of the subquery. I have a subquery to combine
the first names of the households. Does anyone know how I can write my
report? This is what I'm trying to accomplish:

S

Smith, John & Jane
............................................................281-555-1212
1313 Mockingbird Lane

Thanks!

 

[Duane Hookom]

Why can't you change your report's record source so that it doesn't use a
subquery? If you need some assistance, provide the SQL view.

 

[zmerz]

Thanks Evi,

I was looking at another post and the response and I 'm thinking I should
probably do a mail merge into Word. The end result is going to be a printed
directory and I think Word will offer me more formatting. I'll give your
solution a try to see how it works.

zmerz

Sorry zmerz, I never even knew it wasn't possible until now (I've just tried
it out).

Here is one solution which you may be able to improve upon.:

In a new Module in your database paste the following:

Option Compare Database
Option Explicit
Dim GlobalFlag As Variant

Function SetFlag(x As Variant)
If GlobalFlag <> x Then
SetFlag = 1
Else
SetFlag = 0
End If

GlobalFlag = x
End Function

Save and close the Module
Add a text box to your report, in the position where you want the text to
break up.
In Properties name it txtLetter and set Visible to No


In the box type
=Left([Surname],1)
Add another text box, name it txtChange
Make it invisible also
In it, type
=SetFlag(txtLetter)

Set the Can Grow/Can Shrink Property to Yes for the Details section of your
report and for all the controls in it, including these new ones

Click on the Detail section of your report
In Properties click on Events and next to On Format, open up a code page.
Just above End Sub paste


If Me.txtChange = 1 Then
Me.txtLetter.Visible = True
Else
Me.txtLetter.Visible = False
End If

Evi

Hi Evi,

Thanks for the reply. I am unable to use a group header with a subquery;
the report won't allow it. Any other ideas?

In the Sorting Grouping box, type
=Left([LastName],1)
Choose Yes next to Group Header
Evi
I have a report based on a subquery. In the report I would like to break
up
the report when the first letter of the last name changes. I am unable to
group on the last name because of the subquery. I have a subquery to
combine
the first names of the households. Does anyone know how I can write my
report? This is what I'm trying to accomplish:

S

Smith, John & Jane
............................................................281-555-1212
1313 Mockingbird Lane

Thanks!

 

[zmerz]

Hello Duane,

In my database I have a many to one relationship between the people living
in their house and the house itself. In the people's table I have a field
letting me know if the person is the father (1), wife (2), child (3), or
unattached (null). Getting the results from the query for the unattached
people is easy because they occupy one line. However, the spouses occupy two
lines. I wanted to get the spouses names on one line which led me to the
subquery.

SELECT tblPeople.AddressID, Address, tblPeople.PKRelationship,
tblPeople.LastName, tblPeople.FirstName & IIf(tblpeople.PKRelationship=1," &
" & (SELECT dupe.FirstName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2),"") AS
FName, tblPeople.Anniversary
FROM tblPeople RIGHT JOIN tblAddress ON tblPeople.AddressID =
tblAddress.AddressID
WHERE (((tblPeople.PKRelationship)=1)) OR
(((IsNull([tblPeople].[PKRelationship]))<>False))
ORDER BY tblPeople.LastName;

Thanks, zmerz

 

[Duane Hookom]

This will run less efficiently but should work:

SELECT tblPeople.AddressID, Address, tblPeople.PKRelationship,
tblPeople.LastName,
tblPeople.FirstName &
IIf(tblpeople.PKRelationship=1," & " &
DLookup("FirstName","tblPeople","AddressID = " & AddressID & " and
PKRelationship=2"),"") AS FName,
tblPeople.Anniversary
FROM tblPeople RIGHT JOIN tblAddress ON tblPeople.AddressID =
tblAddress.AddressID
WHERE tblPeople.PKRelationship=1 OR
IsNull([tblPeople].[PKRelationship])<>False
ORDER BY tblPeople.LastName;

--
Duane Hookom
Microsoft Access MVP

Hello Duane,

In my database I have a many to one relationship between the people living
in their house and the house itself. In the people's table I have a field
letting me know if the person is the father (1), wife (2), child (3), or
unattached (null). Getting the results from the query for the unattached
people is easy because they occupy one line. However, the spouses occupy two
lines. I wanted to get the spouses names on one line which led me to the
subquery.

SELECT tblPeople.AddressID, Address, tblPeople.PKRelationship,
tblPeople.LastName, tblPeople.FirstName & IIf(tblpeople.PKRelationship=1," &
" & (SELECT dupe.FirstName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2),"") AS
FName, tblPeople.Anniversary
FROM tblPeople RIGHT JOIN tblAddress ON tblPeople.AddressID =
tblAddress.AddressID
WHERE (((tblPeople.PKRelationship)=1)) OR
(((IsNull([tblPeople].[PKRelationship]))<>False))
ORDER BY tblPeople.LastName;

Thanks, zmerz

Why can't you change your report's record source so that it doesn't use a
subquery? If you need some assistance, provide the SQL view.

 

[zmerz]

Wow! That worked great. Now, I'll give Evi's original post a try and see
where that leads me.

Thanks, zmerz

This will run less efficiently but should work:

SELECT tblPeople.AddressID, Address, tblPeople.PKRelationship,
tblPeople.LastName,
tblPeople.FirstName &
IIf(tblpeople.PKRelationship=1," & " &
DLookup("FirstName","tblPeople","AddressID = " & AddressID & " and
PKRelationship=2"),"") AS FName,
tblPeople.Anniversary
FROM tblPeople RIGHT JOIN tblAddress ON tblPeople.AddressID =
tblAddress.AddressID
WHERE tblPeople.PKRelationship=1 OR
IsNull([tblPeople].[PKRelationship])<>False
ORDER BY tblPeople.LastName;

--
Duane Hookom
Microsoft Access MVP

Hello Duane,

In my database I have a many to one relationship between the people living
in their house and the house itself. In the people's table I have a field
letting me know if the person is the father (1), wife (2), child (3), or
unattached (null). Getting the results from the query for the unattached
people is easy because they occupy one line. However, the spouses occupy two
lines. I wanted to get the spouses names on one line which led me to the
subquery.

SELECT tblPeople.AddressID, Address, tblPeople.PKRelationship,
tblPeople.LastName, tblPeople.FirstName & IIf(tblpeople.PKRelationship=1," &
" & (SELECT dupe.FirstName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2),"") AS
FName, tblPeople.Anniversary
FROM tblPeople RIGHT JOIN tblAddress ON tblPeople.AddressID =
tblAddress.AddressID
WHERE (((tblPeople.PKRelationship)=1)) OR
(((IsNull([tblPeople].[PKRelationship]))<>False))
ORDER BY tblPeople.LastName;

Thanks, zmerz

Why can't you change your report's record source so that it doesn't use a
subquery? If you need some assistance, provide the SQL view.

--
Duane Hookom
Microsoft Access MVP


:

I have a report based on a subquery. In the report I would like to break up
the report when the first letter of the last name changes. I am unable to
group on the last name because of the subquery. I have a subquery to combine
the first names of the households. Does anyone know how I can write my
report? This is what I'm trying to accomplish:

S

Smith, John & Jane
...........................................................281-555-1212
1313 Mockingbird Lane

Thanks!