MS ACC 2000: Printing a page header based on data value

[David Dubroff of Mr. PC]

Microsoft Access 2000:

I have a table that contains automobile parts information.

Using the part number field, I plan to sort the car parts in a select query
based on the fourth, fifth, and sixth character positions in the part number
field. The 4th, 5th, and 6th positions should be numbers.

In the select query, I will add a field based on: Mid$(fieldname,4,3)

One particular part number might be as follows: 964145092

This example part number would get sorted by 145. These three digits
together make up the number 145 (one hundred forty-five). This number being
145 is considered as being within the 100's category which stands for a
particular category of car parts. (100 = upholstery 200=structural parts
300 = exterior parts [perhaps])

I want my report to have a label printed before each printed section to
appear as follows: 100's , 200's , 300's , 400's , 500's , 600's , 700's ,
800's , 900's.

How can I make Access 2000 display/print the desired labels before each set
of data begins?

 

[Allen Browne]

1. Change the calculated query field so Access sees it as a number, i.e.:
CLng(Mid([fieldname], 3, 4))

2. In the Sorting And Grouping box in your report, choose this calculated
field, and in the lower pane of the dialog set these properties:
Group Header Yes
Group On Interval
Group Interval 100

3. In the group header section, add a text box with this ControlSource:
=(100 * Int([MyCalcField]/100)) & "'s"

 

[Larry Linson]

In the Query which you use as the RecordSource of the Report, in a
calculated field, determine the "sort number" you describe. Extract the
first digit of that number in another calculated field and group on that
digit... you shouldn't have a great deal of difficulty concatenating a
couple of zeros in the text box that you display in the Group Header.

If you want each group to begin on a new page, use the Force New Page
property of the Group Header.

Larry Linson
Microsoft Access MVP

 

[David Dubroff of Mr. PC]

Using Allen's quick approach, I do have one additional rule that needs to be
included as follows:

I have five (5) parts that begin with the letter "N". How do I get these
five parts to appear in their own "N" group after all of the other numeric
groups that are being grouped within the report by an interval of 100.

I converted the 3 extracted digits to a long integer for reporting purposes
as instructed, but how can I add this one additional rule that if the part
number begins with the letter "N", these parts must appear in their own
group?

Please keep in mind that I am printing a "DSum" grand total at the bottom of
the page from one query, so I would like all of the magic to come from one
query if possible. If not, please include what is needed to still get the
grand total as needed.

Thanks.

1. Change the calculated query field so Access sees it as a number, i.e.:
CLng(Mid([fieldname], 3, 4))

2. In the Sorting And Grouping box in your report, choose this calculated
field, and in the lower pane of the dialog set these properties:
Group Header Yes
Group On Interval
Group Interval 100

3. In the group header section, add a text box with this ControlSource:
=(100 * Int([MyCalcField]/100)) & "'s"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Microsoft Access 2000:

I have a table that contains automobile parts information.

Using the part number field, I plan to sort the car parts in a select query
based on the fourth, fifth, and sixth character positions in the part number
field. The 4th, 5th, and 6th positions should be numbers.

In the select query, I will add a field based on: Mid$(fieldname,4,3)

One particular part number might be as follows: 964145092

This example part number would get sorted by 145. These three digits
together make up the number 145 (one hundred forty-five). This number being
145 is considered as being within the 100's category which stands for a
particular category of car parts. (100 = upholstery 200=structural parts
300 = exterior parts [perhaps])

I want my report to have a label printed before each printed section to
appear as follows: 100's , 200's , 300's , 400's , 500's , 600's ,

700's

,
800's , 900's.

How can I make Access 2000 display/print the desired labels before each set
of data begins?

 

[Allen Browne]

Create another calculated field in your query:
StartsWithN: IIf(Left([fieldname],1) = "N", -1, 0)
This gives you a field that is True when the field stars with N, else False.

Now in the report's Sorting'n'Grouping box, choose the StartsWithN field in
the next row of the dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using Allen's quick approach, I do have one additional rule that needs to be
included as follows:

I have five (5) parts that begin with the letter "N". How do I get these
five parts to appear in their own "N" group after all of the other numeric
groups that are being grouped within the report by an interval of 100.

I converted the 3 extracted digits to a long integer for reporting purposes
as instructed, but how can I add this one additional rule that if the part
number begins with the letter "N", these parts must appear in their own
group?

Please keep in mind that I am printing a "DSum" grand total at the bottom of
the page from one query, so I would like all of the magic to come from one
query if possible. If not, please include what is needed to still get the
grand total as needed.

Thanks.

1. Change the calculated query field so Access sees it as a number, i.e.:
CLng(Mid([fieldname], 3, 4))

2. In the Sorting And Grouping box in your report, choose this calculated
field, and in the lower pane of the dialog set these properties:
Group Header Yes
Group On Interval
Group Interval 100

3. In the group header section, add a text box with this ControlSource:
=(100 * Int([MyCalcField]/100)) & "'s"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Microsoft Access 2000:

I have a table that contains automobile parts information.

Using the part number field, I plan to sort the car parts in a select query
based on the fourth, fifth, and sixth character positions in the part number
field. The 4th, 5th, and 6th positions should be numbers.

In the select query, I will add a field based on: Mid$(fieldname,4,3)

One particular part number might be as follows: 964145092

This example part number would get sorted by 145. These three digits
together make up the number 145 (one hundred forty-five). This number being
145 is considered as being within the 100's category which stands for a
particular category of car parts. (100 = upholstery 200=structural parts
300 = exterior parts [perhaps])

I want my report to have a label printed before each printed section to
appear as follows: 100's , 200's , 300's , 400's , 500's , 600's ,

700's

,
800's , 900's.

How can I make Access 2000 display/print the desired labels before

each
set

of data begins?

 

[David Dubroff of Mr. PC]

Allen...

I do not think I can accomplish what I need on my report from one query.

On my report... I need to print all of the part numbers that do not begin
with the letter "N". These part numbers that do not begin with "N" have to
get sorted based on: Mid$([field],4,3. This much works fine on my report.

I have 42 pages of these parts. On page 42 of the report, I would like to
print the five (5) parts that happen to begin with the letter "N, after the
other parts, of course. Also, I need to print a header above the "N" data
that appears similar to the other headers. The header will be: "N's".
Also, I would like the "N" data to include the same running sum column that
I have been using throughout the 42 pages of other parts.

How do I accomplish the complex "combine" that I need to do within one
report?

Thanks in advance...

Dave

Create another calculated field in your query:
StartsWithN: IIf(Left([fieldname],1) = "N", -1, 0)
This gives you a field that is True when the field stars with N, else False.

Now in the report's Sorting'n'Grouping box, choose the StartsWithN field in
the next row of the dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using Allen's quick approach, I do have one additional rule that needs

to

be
included as follows:

I have five (5) parts that begin with the letter "N". How do I get these
five parts to appear in their own "N" group after all of the other numeric
groups that are being grouped within the report by an interval of 100.

I converted the 3 extracted digits to a long integer for reporting purposes
as instructed, but how can I add this one additional rule that if the part
number begins with the letter "N", these parts must appear in their own
group?

Please keep in mind that I am printing a "DSum" grand total at the

bottom

of
the page from one query, so I would like all of the magic to come from one
query if possible. If not, please include what is needed to still get the
grand total as needed.

Thanks.

1. Change the calculated query field so Access sees it as a number, i.e.:
CLng(Mid([fieldname], 3, 4))

2. In the Sorting And Grouping box in your report, choose this calculated
field, and in the lower pane of the dialog set these properties:
Group Header Yes
Group On Interval
Group Interval 100

3. In the group header section, add a text box with this ControlSource:
=(100 * Int([MyCalcField]/100)) & "'s"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Microsoft Access 2000:

I have a table that contains automobile parts information.

Using the part number field, I plan to sort the car parts in a select
query
based on the fourth, fifth, and sixth character positions in the part
number
field. The 4th, 5th, and 6th positions should be numbers.

In the select query, I will add a field based on: Mid$(fieldname,4,3)

One particular part number might be as follows: 964145092

This example part number would get sorted by 145. These three digits
together make up the number 145 (one hundred forty-five). This number
being
145 is considered as being within the 100's category which stands

for

a

particular category of car parts. (100 = upholstery 200=structural
parts
300 = exterior parts [perhaps])

I want my report to have a label printed before each printed section to
appear as follows: 100's , 200's , 300's , 400's , 500's , 600's , 700's
,
800's , 900's.

How can I make Access 2000 display/print the desired labels before each
set
of data begins?

 

[Allen Browne]

You should be able to do this with one query by breaking the field up, and
then using sorting'n'grouping levels in the report to define the sorting and
grouping you need in your report.

To solve the problem permanently and make performance acceptable, you would
need to break up the field into different fields that store only one thing
each, instead of having a field that is non-atomic.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen...

I do not think I can accomplish what I need on my report from one query.

On my report... I need to print all of the part numbers that do not begin
with the letter "N". These part numbers that do not begin with "N" have to
get sorted based on: Mid$([field],4,3. This much works fine on my report.

I have 42 pages of these parts. On page 42 of the report, I would like to
print the five (5) parts that happen to begin with the letter "N, after the
other parts, of course. Also, I need to print a header above the "N" data
that appears similar to the other headers. The header will be: "N's".
Also, I would like the "N" data to include the same running sum column that
I have been using throughout the 42 pages of other parts.

How do I accomplish the complex "combine" that I need to do within one
report?

Thanks in advance...

Dave

Create another calculated field in your query:
StartsWithN: IIf(Left([fieldname],1) = "N", -1, 0)
This gives you a field that is True when the field stars with N, else False.

Now in the report's Sorting'n'Grouping box, choose the StartsWithN field in
the next row of the dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using Allen's quick approach, I do have one additional rule that needs

to

be
included as follows:

I have five (5) parts that begin with the letter "N". How do I get these
five parts to appear in their own "N" group after all of the other numeric
groups that are being grouped within the report by an interval of 100.

I converted the 3 extracted digits to a long integer for reporting purposes
as instructed, but how can I add this one additional rule that if the part
number begins with the letter "N", these parts must appear in their own
group?

Please keep in mind that I am printing a "DSum" grand total at the

bottom

of
the page from one query, so I would like all of the magic to come from one
query if possible. If not, please include what is needed to still get the
grand total as needed.

Thanks.



1. Change the calculated query field so Access sees it as a number, i.e.:
CLng(Mid([fieldname], 3, 4))

2. In the Sorting And Grouping box in your report, choose this calculated
field, and in the lower pane of the dialog set these properties:
Group Header Yes
Group On Interval
Group Interval 100

3. In the group header section, add a text box with this ControlSource:
=(100 * Int([MyCalcField]/100)) & "'s"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Microsoft Access 2000:

I have a table that contains automobile parts information.

Using the part number field, I plan to sort the car parts in a select
query
based on the fourth, fifth, and sixth character positions in the part
number
field. The 4th, 5th, and 6th positions should be numbers.

In the select query, I will add a field based on: Mid$(fieldname,4,3)

One particular part number might be as follows: 964145092

This example part number would get sorted by 145. These three digits
together make up the number 145 (one hundred forty-five). This number
being
145 is considered as being within the 100's category which stands

for

a
particular category of car parts. (100 = upholstery 200=structural
parts
300 = exterior parts [perhaps])

I want my report to have a label printed before each printed

section
to

appear as follows: 100's , 200's , 300's , 400's , 500's , 600's ,
700's
,
800's , 900's.

How can I make Access 2000 display/print the desired labels before each
set
of data begins?