Link to home
Start Free TrialLog in
Avatar of duta
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
Avatar of rockiroads
rockiroads
Flag of United States of America image

Regarding underlines, there is the double underline function here

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


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
Avatar of duta
duta

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!

Avatar of duta

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!
Avatar of duta

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
Avatar of duta

ASKER

Do I need to copy and paste the above function into tag?

Thanks!
Avatar of duta

ASKER

Do I need to use both "Function MakeLines(rpt As Report)" and "Private Sub Detail_Print"?

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!!!)
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
Avatar of duta

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
No, just dbl goes into the tag property

the other bits go into the module code in vba
Avatar of duta

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
Avatar of duta

ASKER

Should I use Module (as seen below)?
VBA-Module.png
Avatar of duta

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
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.

ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now do what I said earlier about the On Print event and just add

MakeLines Me

in the code for Detail_print
Avatar of duta

ASKER

Genius, where can I find "On Print" event?  

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
Avatar of duta

ASKER

Genius, I could not find it.

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
Avatar of duta

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!
Avatar of duta

ASKER

Below is a screen shot of module, and code.  Did I place the code in a right places?
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
Avatar of duta

ASKER

Genius, I ran it, and got the error message as below:


compiled-Error.png
do

MakeLines Me

dont put it in brackets
see screenshot
ul.jpg
Avatar of duta

ASKER

Genius, I modified my code  exactly as you kindly showed me. Still I got the same error as seen below:
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
Avatar of duta

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
Avatar of duta

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
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
Avatar of duta

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!
Avatar of duta

ASKER

Awesome!
Hi, I dont know what event as it would be in report but cant find anything suitable.