skipping blank lines - again

[lynne]

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

 

[Terry]

Lynne,

You could replicate the control(s) in your report so that
you have one for the Description (eg Base Salary) and one
for the amount. If the amount is displayed in it's own
control it will use that control's formatting properties -
currency 2 decimal places etc.

When a number of some kind is used as part of a string it
needs to be formatted - =[description] & " " & Format
([amount],"$0.00") to have the display as you require.
Base Salary $45.25

hth,

Terry

 

[Lenore]

Could you create a field in the datasource for the
report - ie the query and do an IIF[fieldname]=0, " ",
[fieldname] then put that field in the report.

-----Original Message-----
Lynne,

You could replicate the control(s) in your report so that
you have one for the Description (eg Base Salary) and one
for the amount. If the amount is displayed in it's own
control it will use that control's formatting properties -
currency 2 decimal places etc.

When a number of some kind is used as part of a string it
needs to be formatted - =[description] & " " & Format
([amount],"$0.00") to have the display as you require.
Base Salary $45.25

hth,

Terry

-----Original Message-----
This is a long post - please bear with me.

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
.

.