Displaying controls as separated values in boxes on a report

[Guest]

I have a text field in a table and want to display it on a report within boxes.

For example, the field might say TRLU123456

I need to get the T in a box or put a border round it, then the R etc etc etc.

The field length can change so if I just draw boxes over the control the
data may move around underneath it?

Any ideas please

 

[Jeff Boyce]

Virginia

Are you saying that the field holds more than one "fact"? If so, this is
contrary to basic database design principles ("one fact, one field").

Because you are trying to isolate out pieces, I'm suspecting the above.

You explained "how" you wanted to do something (a box around the T), but not
why. What will having a box around the T allow you/your users to do?

I ask, not out of curiosity, but because if we better understand what
business need you are trying to solve in this manner, we may be able to
offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

I have a text field in a table and want to display it on a report within boxes.

For example, the field might say TRLU123456

I need to get the T in a box or put a border round it, then the R etc etc etc.

The field length can change so if I just draw boxes over the control the
data may move around underneath it?

Any ideas please

Easy enough.

Let's assume the largest amount of text will be 16 characters.
Adjust as necessary.

Add 16 unbound text controls to your report.

An easy way to add 16 controls is to add 1. Select it Copy and paste.
Select these 2, Copy and Paste, Select these 4, Copy and paste, etc.

Delete all of their labels. Size the controls to whatever height and
width will work. Name them "Text1", "Text2", etc... "Text16".
Position them in order, left to right.
Space them using Format + Horizontal Spacing Make Equal. Align them.

Set their Border Style to Solid, Border Width to 1 pt size.

Code the Report Section's Format event.
If they're in the Detail section, then:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
Dim intY As Integer
intX = 1

For intX = 1 To Len([FieldName])
Me("Text" & intX) = Mid([FieldName], intX, 1)
Me("Text" & intX).Visible = True
Next intX

' To hide extra boxes or to show empty boxes
For intY = intX To 16
Me("Text" & intY).Visible = False ' To hide extra boxes
' Me("Text" & intY) = "" ' To show the box but delete the text
Next intY

End Sub

Set the control's FontSize to one that will match the size of the
box. Set the Alignment to Center.

 

[Guest]

Hi Jeff, thanks for trying to help - Freds reply has worked, so all fixed.
cheers

 

[Guest]

Thanks Fred, this works brilliantly!

I have a text field in a table and want to display it on a report within boxes.

For example, the field might say TRLU123456

I need to get the T in a box or put a border round it, then the R etc etc etc.

The field length can change so if I just draw boxes over the control the
data may move around underneath it?

Any ideas please

Easy enough.

Let's assume the largest amount of text will be 16 characters.
Adjust as necessary.

Add 16 unbound text controls to your report.

An easy way to add 16 controls is to add 1. Select it Copy and paste.
Select these 2, Copy and Paste, Select these 4, Copy and paste, etc.

Delete all of their labels. Size the controls to whatever height and
width will work. Name them "Text1", "Text2", etc... "Text16".
Position them in order, left to right.
Space them using Format + Horizontal Spacing Make Equal. Align them.

Set their Border Style to Solid, Border Width to 1 pt size.

Code the Report Section's Format event.
If they're in the Detail section, then:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
Dim intY As Integer
intX = 1

For intX = 1 To Len([FieldName])
Me("Text" & intX) = Mid([FieldName], intX, 1)
Me("Text" & intX).Visible = True
Next intX

' To hide extra boxes or to show empty boxes
For intY = intX To 16
Me("Text" & intY).Visible = False ' To hide extra boxes
' Me("Text" & intY) = "" ' To show the box but delete the text
Next intY

End Sub

Set the control's FontSize to one that will match the size of the
box. Set the Alignment to Center.