Prev: Subreport Keeps Repeating Itself
Next: blank subreports appearing on every record of report
From: David on 1 Jun 2010 02:35
What happens if your data is not in alphabetical order but in an order that you want to have this merge field criteria applied? What if you have multiple merges that you would like to perform? Here is my data:

Raw Data is to the left. For sorting purposes, I put "C" data in before "B" but it does not have to be sequential. Keep in mind, the order of the raw data is important and I do not want to change this when I merge the levels afterwards. My merging criteria would something like this where is x is the letter and * is the number:

C1 - C8 merges to C0
C11 - 13 to C1
B1 - B5 to B0
B11 - B16 to C1

C0 and C1 combine to C
B0 and B1 combine to B

Finally both can be merged on the 3rd level to Alpha. How can you program this?

Thanks,
David

Raw Data 1st Level 2nd Level 3rd Level
C1 C0 C Alpha
C2
C3
C4
C5
C6
C7
C8
C11 C1
C12
C13
B1 B0 B
B2
B3
B4
B5
B11 B1
B12
B13
B14
B15
B16




Duane Hookom wrote:

The month will need to fall into alignment with one of the records in
17-Nov-09

The month will need to fall into alignment with one of the records in the
detail section.

Create a group header section on the month and add a text box:
Name: txtMthRecords
Control Source: =Count(*)

Set the month group header section's Visible property to No.

Add a text box to the detail section:
Name: txtMthCount
Control Source: =1
Running Sum: Over Group
Visible: No

Make sure you have the Month bound to a control in the detail section.
Name: txtMth

Add this code to the On Format event of the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intMidRecord As Integer
intMidRecord = (Me.txtMthRecords + 1) \ 2
Me.txtMth.Visible = (intMidRecord = Me.txtMthCount)
Me.Line (Me.txtMth.Left, 0)-Step(0, Me.Height)
Me.Line (Me.txtMth.Left + Me.txtMth.Width, 0)-Step(0, Me.Height)
If Me.txtMthCount = 1 Then 'first record in month
Me.Line (Me.txtMth.Left, 0)-Step(Me.txtMth.Width, 0)
End If
If Me.txtMthCount = Me.txtMthRecords Then 'last record in month
Me.Line (Me.txtMth.Left, Me.Height)-Step(Me.txtMth.Width, 0)
End If
End Sub


--
Duane Hookom
Microsoft Access MVP


"Robbie Doo" wrote:

Previous Posts In This Thread:

On Thursday, November 05, 2009 2:30 PM
Robbie Doo wrote:

Merge fields into one
Is it possible to merge the fields and center (in the same column) it in
Access report the way you do in Excel?

If so, would it be done by code?

On Thursday, November 05, 2009 3:26 PM
Duane Hookom wrote:

You can use a text box with a control source like:=[FirstName] & " " &
You can use a text box with a control source like:
=[FirstName] & " " & [LastName]
or
=[City] & ", " & [State] & " " & [ZipCode]

Set the alignment of the text box to centered and make sure the name of the
text box is not the name of a field.

--
Duane Hookom
Microsoft Access MVP


"Robbie Doo" wrote:

On Thursday, November 05, 2009 8:01 PM
Robbie Doo wrote:

Thank you Duane but the field I want merged is the same field.
Thank you Duane but the field I want merged is the same field. I used "Hide
Duplicates" but it gives me empty boxes until the next record. In Excel I can
merge the cells with the same name into one and Center it whether it is column
or row. I know how to do this in a row in access, because it is easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

"Duane Hookom" wrote:

On Friday, November 06, 2009 2:32 PM
Jeff Boyce wrote:

We are not there. We cannot see your tables.
We are not there. We cannot see your tables. "How" depends on "what">

"the field I want merged is the same field" is a bit cryptic.

If you do not want something like Duane offered (merging FirstName & LastName
fields), are you saying that you have data stored in multiple records, all
in the same field, and you want to concatenate (merge) "down" rather than
"across"?

(remember, Access is NOT a spreadsheet. Something you know how to do in
Excel may require something totally different in Access.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

On Friday, November 06, 2009 11:45 PM
Duane Hookom wrote:

Please take the time to type in some records of raw data and then how youwant
Please take the time to type in some records of raw data and then how you
want them displayed in your report.

--
Duane Hookom
Microsoft Access MVP


"Robbie Doo" wrote:

On Monday, November 09, 2009 10:56 AM
Robbie Doo wrote:

Thank you Duane,I would like the report to be something like
Thank you Duane,

I would like the report to be something like this:
----------------------------
| A1 |
Jan | A2 |
| A3 |
---------------------------
| B1 |
Feb | B2 |
| B3 |
---------------------------

The month Column is merged and centered vertically. Is it possible?


"Duane Hookom" wrote:

On Monday, November 09, 2009 6:32 PM
duanehookom wrote:

I still cannot see your "raw data".
I still cannot see your "raw data". You only presented how you want
them displayed in your report. You are giving us the impression there
always three records per month. Is this true? Are there borders
required?

Duane Hookom
MS Access MVP

wrote:
ou
"Hide
el I can
s column
o work
en
e of the
) it in

On Saturday, November 14, 2009 12:57 AM
Robbie Doo wrote:

No, 3 is only in my example. Records can be 2 or more per month.
No, 3 is only in my example. Records can be 2 or more per month. What I am
looking for is borders around the months and months to be centered (if
possible). Putting borders around the other date (i.e., A1, A2...) is easy.
I am just struggling with the month columns. I use the "Hide Duplicates"
feature, which gives me the month and empty bordered fields until the next
month. I just want to get rid of those extra empty bordered areas and merge
all into one and center the month. Is this possible in Access?

Thank you for all your help.

"duanehookom" wrote:

On Tuesday, November 17, 2009 12:56 AM
Duane Hookom wrote:

The month will need to fall into alignment with one of the records in
The month will need to fall into alignment with one of the records in the
detail section.

Create a group header section on the month and add a text box:
Name: txtMthRecords
Control Source: =Count(*)

Set the month group header section's Visible property to No.

Add a text box to the detail section:
Name: txtMthCount
Control Source: =1
Running Sum: Over Group
Visible: No

Make sure you have the Month bound to a control in the detail section.
Name: txtMth

Add this code to the On Format event of the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intMidRecord As Integer
intMidRecord = (Me.txtMthRecords + 1) \ 2
Me.txtMth.Visible = (intMidRecord = Me.txtMthCount)
Me.Line (Me.txtMth.Left, 0)-Step(0, Me.Height)
Me.Line (Me.txtMth.Left + Me.txtMth.Width, 0)-Step(0, Me.Height)
If Me.txtMthCount = 1 Then 'first record in month
Me.Line (Me.txtMth.Left, 0)-Step(Me.txtMth.Width, 0)
End If
If Me.txtMthCount = Me.txtMthRecords Then 'last record in month
Me.Line (Me.txtMth.Left, Me.Height)-Step(Me.txtMth.Width, 0)
End If
End Sub


--
Duane Hookom
Microsoft Access MVP


"Robbie Doo" wrote:

On Wednesday, November 18, 2009 8:49 PM
Robbie Doo wrote:

Thank you Duance for all your help."Duane Hookom" wrote:
Thank you Duance for all your help.

"Duane Hookom" wrote:

On Thursday, November 19, 2009 1:25 PM
Robbie Doo wrote:

Thank you Duane. This is exactly what I was looking for.
Thank you Duane. This is exactly what I was looking for. However, when the
month is continued onto the next page the bottom part of the page does not
have a border. Is it possible to close the border then continue onto the next
page even if no month-name will appear until the next one?

Thanx again for all your help.

"Duane Hookom" wrote:

On Friday, November 20, 2009 11:46 AM
Duane Hookom wrote:

I would just group by month and choose to keep the whole group together.
I would just group by month and choose to keep the whole group together.
--
Duane Hookom
Microsoft Access MVP


"Robbie Doo" wrote:

On Monday, November 30, 2009 8:16 PM
Robbie Doo wrote:

Duane,The grouping worked just fine the way you suggested.
Duane,

The grouping worked just fine the way you suggested. I just do not know why
the month-grouping would not create a border at the bottom of the page and
would continue until the next page when it hits the next month?!

"Duane Hookom" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Telerik RadControls For Silverlight (3 and 4) Q1 2010
http://www.eggheadcafe.com/tutorials/aspnet/6566fc91-a77c-4553-8ac2-7a7fa36e63a1/telerik-radcontrols-for-s.aspx
 | 
Pages: 1
Prev: Subreport Keeps Repeating Itself
Next: blank subreports appearing on every record of report