skipping blank lines - again

From: Terry (anonymous_at_discussions.microsoft.com)
Date: 03/31/04 

Date: Tue, 30 Mar 2004 18:06:56 -0800

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

Relevant Pages
Loading