duta
asked on
MS Access Report Format: How to put a single underline, double underline, and how to replace minus with parenthesis?
Dear Experts:
Hi!
I just created an Access (2007) report.
I would like to learn from you geniuses:
1. How to put a single underline
2. How to put a double underline
3. How to display a negative number in the following format:
- $20,000 --> $(20,000)
Thanks a lot
Regarding formatting of numbers, add this to the format property of your control
#,##0.00;(#,##0.00)
Note, may need to be wrapped with double quotes
#,##0.00;(#,##0.00)
Note, may need to be wrapped with double quotes
ASKER
Genius rockiroads, your tip worked great!
On the code to single underline and double underlien:
I got the following code from the link you gave me:
____________________
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
rpt.Line (ctl.Left, ctl.Top + ctl.Height +
dblSpacing)-Step(ctl.Width , 0)
End If
Next
End Function
Edit the tag property of the controls you want to mark. Use bl for bottom
line, tl for top line, and dbl for double bottom line. Then add this code to
the section containing the controls.
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Call MakeLines(Me)
End Sub
__________________________ ________
Can you kindly give me step-by-step instructions regarding how to do with that?
For instance, where do I place it?
Thanks a lot!
On the code to single underline and double underlien:
I got the following code from the link you gave me:
____________________
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
rpt.Line (ctl.Left, ctl.Top + ctl.Height +
dblSpacing)-Step(ctl.Width
End If
Next
End Function
Edit the tag property of the controls you want to mark. Use bl for bottom
line, tl for top line, and dbl for double bottom line. Then add this code to
the section containing the controls.
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Call MakeLines(Me)
End Sub
__________________________
Can you kindly give me step-by-step instructions regarding how to do with that?
For instance, where do I place it?
Thanks a lot!
ASKER
YOU WROTE: You need to update the tag property of the controls in your report
MY QUESTION: Where can I locate "tag property of the controls"?
What did you mean by "the controls"?
Thanks!
MY QUESTION: Where can I locate "tag property of the controls"?
What did you mean by "the controls"?
Thanks!
ASKER
I guess "the control" in this care refers to the textbox.
Am I right?
I found "tag" in the property of the textbox as seen below.
tag.png
Am I right?
I found "tag" in the property of the textbox as seen below.
tag.png
ASKER
Do I need to copy and paste the above function into tag?
Thanks!
Thanks!
ASKER
Do I need to use both "Function MakeLines(rpt As Report)" and "Private Sub Detail_Print"?
Thanks again!
Thanks again!
Hi
Yes thats the property you need to add. Try this first, in a couple of controls add in the text dbl (no quotes needed)
then run your report, it should double underline it (hopefully!!!)
Yes thats the property you need to add. Try this first, in a couple of controls add in the text dbl (no quotes needed)
then run your report, it should double underline it (hopefully!!!)
Regarding the code, yes both required.
Detail_Print is an event of the report. If you are in design view of the report, look at the properties, click on the events tab then find Detail on the dropdown then you will see Print
Detail_Print is an event of the report. If you are in design view of the report, look at the properties, click on the events tab then find Detail on the dropdown then you will see Print
ASKER
Am I supposed to type in only "dbl" in the tag as seen blow?
In addition, I need to enter theentire code of the above function Makelines" in the tag too?
Thanks!
tag-dbl.png
In addition, I need to enter theentire code of the above function Makelines" in the tag too?
Thanks!
tag-dbl.png
No, just dbl goes into the tag property
the other bits go into the module code in vba
the other bits go into the module code in vba
ASKER
Should both of the two functions below go into module code in VBA?
______________________
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
rpt.Line (ctl.Left, ctl.Top + ctl.Height +
dblSpacing)-Step(ctl.Width , 0)
End If
Next
End Function
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Call MakeLines(Me)
End Sub
______________________
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
rpt.Line (ctl.Left, ctl.Top + ctl.Height +
dblSpacing)-Step(ctl.Width
End If
Next
End Function
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Call MakeLines(Me)
End Sub
ASKER
Should I use Module (as seen below)?
VBA-Module.png
VBA-Module.png
ASKER
After entering the code (one or both?) in the Module (as seen below), should I give a certain name when saving it?
Thanks!
Module-Code.png
Thanks!
Module-Code.png
okay, once u add in the tag property, do what I said, find the On Print event for the detail from the properties
then click on On print, a button appears, click that and select Code Builder, this takes u to the vba window. Now paste your code so it essentially looks like this
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
MakeLines Me
End Sub
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width , 0)
rpt.Line (ctl.Left, ctl.Top + ctl.Height + dblSpacing)-Step(ctl.Width , 0)
End If
Next
End Function
Ideally we should be the function MakeLines in its own module but will do that later. Lets get it going first.
then click on On print, a button appears, click that and select Code Builder, this takes u to the vba window. Now paste your code so it essentially looks like this
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
MakeLines Me
End Sub
Function MakeLines(rpt As Report)
Dim ctl As Control
Dim dblSpacing As Double 'space between double underline
dblSpacing = 30
For Each ctl In rpt.Controls
If ctl.Tag = "bl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
End If
If ctl.Tag = "tl" Then
rpt.Line (ctl.Left, ctl.Top)-Step(ctl.Width, 0)
End If
If ctl.Tag = "dbl" Then
rpt.Line (ctl.Left, ctl.Top + ctl.Height)-Step(ctl.Width
rpt.Line (ctl.Left, ctl.Top + ctl.Height + dblSpacing)-Step(ctl.Width
End If
Next
End Function
Ideally we should be the function MakeLines in its own module but will do that later. Lets get it going first.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Now do what I said earlier about the On Print event and just add
MakeLines Me
in the code for Detail_print
MakeLines Me
in the code for Detail_print
ASKER
Genius, where can I find "On Print" event?
I could not find it in the Property.
I could not find it in the Property.
in design view of report, when u see the list of properties, the top part of it has a combo. find Detail in there
Ensure you have also selected Events in the list of tabs
Ensure you have also selected Events in the list of tabs
ASKER
Genius, I could not find it.
In the controls panel (below), which button should I click it?
Thanks!
controls.png
In the controls panel (below), which button should I click it?
Thanks!
controls.png
No thats not it
go to design view of report (right click on report and select it)
then you see properties window come up
find detail from the dropdown in combo
then click on the events tab
thats where it is
go to design view of report (right click on report and select it)
then you see properties window come up
find detail from the dropdown in combo
then click on the events tab
thats where it is
ASKER
Genius, I located Details. I am trying to place the code you gave me in the right places.
I will update you on my progress very soon!
I will update you on my progress very soon!
ASKER
Below is a screen shot of module, and code. Did I place the code in a right places?
Module-report.png
Module-report.png
You can put it in one of two places but to make it reusable, place in Module 1 instead of where it is now.
Nevertheless, give it a go now. run this report
Nevertheless, give it a go now. run this report
ASKER
do
MakeLines Me
dont put it in brackets
MakeLines Me
dont put it in brackets
see screenshot
ul.jpg
ul.jpg
ASKER
Genius, I modified my code exactly as you kindly showed me. Still I got the same error as seen below:
Latest-Error.png
Latest-Error.png
from the vba editor, can u click on the Debug menu option then select Compile
any errors, it should hilite the line, can u post it here pls
if no errors then nothing should happen
Please see attached example rename file from underline.mdb to underline.accdb as this is in access 2007 since ee will not let me upload accdb files
underline.mdb
any errors, it should hilite the line, can u post it here pls
if no errors then nothing should happen
Please see attached example rename file from underline.mdb to underline.accdb as this is in access 2007 since ee will not let me upload accdb files
underline.mdb
ASKER
Genius, I went to Debug --> Clicked "Compile Waren 97". Nothing has happened.
Notably, "Compile Waren p7" tab has remained dim.
Thank you so much for your kind, patient help!
debug.png
Notably, "Compile Waren p7" tab has remained dim.
Thank you so much for your kind, patient help!
debug.png
ASKER
Genius, I opened your file and found a long line. I would like to specifically underline number like 20, 40 as seen in the screen shot below.
under-number.png
under-number.png
U need to go to Print Preview, is that what you did?
if u want to underline numbers, you need to put in bl or dbl in the tag property
ASKER
Genius, when viewing the report in the Print Preview mode, I can see an underline that I wanted to see.
But the line is not displayed in Report View. Why is it?
How can I make the line displayed in Report Mode too?
Thanks!
But the line is not displayed in Report View. Why is it?
How can I make the line displayed in Report Mode too?
Thanks!
ASKER
Awesome!
Hi, I dont know what event as it would be in report but cant find anything suitable.
https://www.experts-exchange.com/questions/20940199/double-underline.html?cid=236&anchorAnswerId=10733366#a10733366
This could be adapted to create single underlines also
You need to update the tag property of the controls in your report and give them a value which determines whether underlined or not.
You got bl, tl and dbl - latter being double underline, Im guessing bl is single underline