• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1327
  • Last Modified:

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
0
duta
Asked:
duta
  • 19
  • 17
1 Solution
 
rockiroadsCommented:
Regarding underlines, there is the double underline function here

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20940199.html?cid=236#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


0
 
rockiroadsCommented:
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
 
dutaAuthor Commented:
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!

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
dutaAuthor Commented:
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!
0
 
dutaAuthor Commented:
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
0
 
dutaAuthor Commented:
Do I need to copy and paste the above function into tag?

Thanks!
0
 
dutaAuthor Commented:
Do I need to use both "Function MakeLines(rpt As Report)" and "Private Sub Detail_Print"?

Thanks again!
0
 
rockiroadsCommented:
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!!!)
0
 
rockiroadsCommented:
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
0
 
dutaAuthor Commented:
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
0
 
rockiroadsCommented:
No, just dbl goes into the tag property

the other bits go into the module code in vba
0
 
dutaAuthor Commented:
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
0
 
dutaAuthor Commented:
Should I use Module (as seen below)?
VBA-Module.png
0
 
dutaAuthor Commented:
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
0
 
rockiroadsCommented:
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.

0
 
rockiroadsCommented:
Ok, backtrack on what I said. You have managed to get a module going

Ok, in module 2 paste this code underneath option compare database



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
0
 
rockiroadsCommented:
Now do what I said earlier about the On Print event and just add

MakeLines Me

in the code for Detail_print
0
 
dutaAuthor Commented:
Genius, where can I find "On Print" event?  

I could not find it in the Property.
0
 
rockiroadsCommented:
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
0
 
dutaAuthor Commented:
Genius, I could not find it.

In the controls panel (below), which button should I click it?

Thanks!
controls.png
0
 
rockiroadsCommented:
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
0
 
dutaAuthor Commented:
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!
0
 
dutaAuthor Commented:
Below is a screen shot of module, and code.  Did I place the code in a right places?
Module-report.png
0
 
rockiroadsCommented:
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
0
 
dutaAuthor Commented:
Genius, I ran it, and got the error message as below:


compiled-Error.png
0
 
rockiroadsCommented:
do

MakeLines Me

dont put it in brackets
0
 
rockiroadsCommented:
see screenshot
ul.jpg
0
 
dutaAuthor Commented:
Genius, I modified my code  exactly as you kindly showed me. Still I got the same error as seen below:
Latest-Error.png
0
 
rockiroadsCommented:
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
0
 
dutaAuthor Commented:
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
0
 
dutaAuthor Commented:
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
0
 
rockiroadsCommented:
U need to go to Print Preview, is that what you did?
0
 
rockiroadsCommented:
if u want to underline numbers, you need to put in bl or dbl in the tag property
0
 
dutaAuthor Commented:
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!
0
 
dutaAuthor Commented:
Awesome!
0
 
rockiroadsCommented:
Hi, I dont know what event as it would be in report but cant find anything suitable.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 19
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now