skipping blank lines - again

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: lynne (anonymous_at_discussions.microsoft.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 12:59:22 -0800

This is a long post - please bear with me.
> I have a report that lists various employee benefits. If
> a particular employee doesn't have a particular benefit,
> my report originally printed the label for that benefit
> with 0.00 in the amount field. Now they want me to
> change it so that if the employee doesn't have a
> benefit it just skips it altogether. I tried setting
> the fields and their labels to invisible in the ON
> FORMAT property if the amount is 0 but it leaves
> spaces. How can I get it to print only those benefits
> that the employee has without leaving big gaps in my
> columns?
>
> I should have mentioned that next to my column listing
> benefits, I have a graph showing the relation of total
> benefits to total salary. When the graph is there, the
> CanShrink won't work. If I remove the graph, the spacing
> will shrink but the remaining items aren't evenly spaced
> ie if it skips 4 fields the space is larger than if it
> skips 1 or 2 fields. The result is a very uneven look.
> Is there some way to even out the spacing and keep my
> graph?

CanShrink didn't work and putting it in a subform didn't
work but I found a MS white paper with code that I
modified for my report -

Function INFOBLOCK$(BASE, LONGPAY, EXTRACOMP, CERT, PIP,
SICKLEAVE, ACT1341, VAC, FVAC, Retire, SSN, MEDI, HEALTH,
HANDW, emplnumber)
   Dim L1$, L2$, L3$, L4$, L5$, L6$, L7$, L8$, L9$, L10$,
L11$, L12$, L13$, L14$, L15$, L16$, CR$

CR$ = Chr(13) & Chr(10) 'Carriage return and line feed.
  
L1$ = IIf(ISB(BASE), "", ("Base Salary" & " " & BASE &
CR$))
L2$ = IIf(ISB(LONGPAY), "", ("Longevity Pay" & " " &
LONGPAY & CR$))
L3$ = IIf(ISB(EXTRACOMP), "", ("Extra Compensation" & " "
& EXTRACOMP & CR$))
L4$ = IIf(ISB(CERT), "", ("National Certification" & " "
& CERT & CR$))
L5$ = IIf(ISB(PIP), "", ("PIPS" & " " & PIP & CR$))
L6$ = IIf(ISB(SICKLEAVE), "", ("Sick Leave" & " " &
SICKLEAVE & CR$))
L7$ = IIf(ISB(ACT1341), "", ("Act 1341 Sick Leave" & " "
& ACT1341 & CR$))
L8$ = IIf(ISB(VAC), "", ("Annual Vacation" & " " & VAC &
CR$))
L9$ = IIf(ISB(FVAC), "", ("Frozen Vacation" & " " & FVAC
& CR$))
L10$ = IIf(ISB(Retire), "", ("Retirement" & " " & Retire
& CR$))
L11$ = IIf(ISB(SSN), "", ("Social Security" & " " & SSN &
CR$))
L12$ = IIf(ISB(MEDI), "", ("Medicare" & " " & MEDI & CR$))
L13$ = IIf(ISB(HEALTH), "", ("Health Insurance" & " " &
HEALTH & CR$))
L14$ = IIf(ISB(HANDW), "", ("Health & Welfare" & " " &
HANDW & CR$))
L15$ = IIf(ISB(TotalComp), "", ("Total Compensation"
& " " & TotalComp & CR$))
  
INFOBLOCK = L1$ & L2$ & L3$ & L4$ & L5$ & L6$ & L7$ & L8$
& L9$ & L10$ & L11$ & L12$ & L13$ & L14$ & L15
$ 'Concatenate the strings.

End Function

Much to my amazement - IT WORKS. The lines are gone but I
am left with output that looks like this...

Base Salary 30580
Sick Leave 1635.3
Retirement 4220.04
Health & Welfare 300

Is there some way that I can modify the code still
further to 1) get my amounts in currency format and 2)
space them out in neat columns?
I really appreciate all the help you guys have been
giving me.
Thanks. Lynne

Quantcast