Merge fields into one

[Robbie Doo]

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?

 

[Duane Hookom]

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.

 

[Robbie Doo]

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

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

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?

 

[Jeff Boyce]

We aren't there. We can't see your tables. "How" depends on "what">

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

If you don't 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.

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's
column
or row. I know how to do this in a row in access, because it's easy to
work
horizontally in the Detail section. However, the case is different when
working with columns.

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

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?

 

[Duane Hookom]

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

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

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

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?

 

[Robbie Doo]

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?

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

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

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


:

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?

 

[duanehookom]

I still can't 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

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?

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

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.
:
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.
--
DuaneHookom
Microsoft Access MVP
:
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?- Hide quoted text -

- Show quoted text -

 

[Robbie Doo]

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

I still can't 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

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?

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

"Robbie Doo" wrote:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

"DuaneHookom" wrote:

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.

"Robbie Doo" wrote:

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?- Hide quoted text -

- Show quoted text -

.

 

[Duane Hookom]

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

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

I still can't 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

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?



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

--
DuaneHookom
Microsoft Access MVP

:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

:

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.

--
DuaneHookom
Microsoft Access MVP

:

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?- Hide quoted text -

- Show quoted text -

.

 

[Robbie Doo]

Thank you Duance for all your help.

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

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

I still can't 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

On Nov 9, 9:56 am, Robbie Doo <[email protected]>
wrote:
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?



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

--
DuaneHookom
Microsoft Access MVP

:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

:

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.

--
DuaneHookom
Microsoft Access MVP

:

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?- Hide quoted text -

- Show quoted text -

.

 

[Robbie Doo]

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 doesn't
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.

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

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

I still can't 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

On Nov 9, 9:56 am, Robbie Doo <[email protected]>
wrote:
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?



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

--
DuaneHookom
Microsoft Access MVP

:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

:

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.

--
DuaneHookom
Microsoft Access MVP

:

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?- Hide quoted text -

- Show quoted text -

.

 

[Duane Hookom]

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

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 doesn't
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.

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

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

:

I still can't 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

On Nov 9, 9:56 am, Robbie Doo <[email protected]>
wrote:
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?



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

--
DuaneHookom
Microsoft Access MVP

:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

:

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.

--
DuaneHookom
Microsoft Access MVP

:

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?- Hide quoted text -

- Show quoted text -

.

 

[Robbie Doo]

Duane,

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

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

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 doesn't
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.

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


:

No, 3 is only in my example. Records can be 2 or more per month. What I'm
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'm 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.

:

I still can't 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

On Nov 9, 9:56 am, Robbie Doo <[email protected]>
wrote:
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?



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

--
DuaneHookom
Microsoft Access MVP

:

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's column
or row. I know how to do this in a row in access, because it's easy to work
horizontally in the Detail section. However, the case is different when
working with columns.

:

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.

--
DuaneHookom
Microsoft Access MVP

:

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?- Hide quoted text -

- Show quoted text -

.

 

[David Anderson]

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


:

Previous Posts In This Thread:

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?

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


:

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.

:

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.

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


:

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?


:

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

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.

:

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


:

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

:

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.

:

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


:

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?!

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Telerik RadControls For Silverlight (3 and 4) Q1 2010
http://www.eggheadcafe.com/tutorial...2-7a7fa36e63a1/telerik-radcontrols-for-s.aspx