thandel
asked on
Dot leaders between two labels in report
I have a MS Access 2003 report with two labels. The label on the left is left formatted and the label on the right is right formatted
EX:
BLUE 454343123
GREEN 34234
YELLOW 1231
I would like to have dot leaders between these fields EX:
BLUE .................. 454343123
GREEN ........................ 34234
YELLOW ........................ 1231
Sometimes a line will not have data do I want to ensure the leader dot are not displayed.
I was using str = Replace(String(14, "X"), "X", " . . ") to fill which was working OK but wasn't even and could get it to fill fully without moving text out of the label.
Any suggestions.
EX:
BLUE 454343123
GREEN 34234
YELLOW 1231
I would like to have dot leaders between these fields EX:
BLUE .................. 454343123
GREEN ........................ 34234
YELLOW ........................ 1231
Sometimes a line will not have data do I want to ensure the leader dot are not displayed.
I was using str = Replace(String(14, "X"), "X", " . . ") to fill which was working OK but wasn't even and could get it to fill fully without moving text out of the label.
Any suggestions.
ASKER
OK but the bigger issue is how to add the leader dots in between the fields.. what is the best approach?
I know of two ways of handing this. If your using a mono-spaced font, it's fairly easy:
1. Control filled with dots
2. Control with a source set to:
=[Control1] & Right$([ControlWithDots], 60-Len([Control1]) - Len([Control2])) & [Control2]
Think I remembered that right.
If your not using a mono-spaced font, you need to adjust the width of the right hand control position and width. I can work up an example on this if it's needed.
Jim.
1. Control filled with dots
2. Control with a source set to:
=[Control1] & Right$([ControlWithDots], 60-Len([Control1]) - Len([Control2])) & [Control2]
Think I remembered that right.
If your not using a mono-spaced font, you need to adjust the width of the right hand control position and width. I can work up an example on this if it's needed.
Jim.
ASKER
OK I think both sides need control dots as one is left justified and the other is right justified...no?
Actually no.
1. Create one control with all the dots. ie. if you want a 60 character wide result, create a control with 60 dots in the control source.
2. Have control1 and control2 with the data. Control1 will contain what is on the left side, control2 will contain what is on the right. Both are left justified.
3. Hide all those controls.
4. Create one control (visible), with the control source of:
=[Control1] & Right$([ControlWithDots], 60-Len([Control1]) - Len([Control2])) & [Control2]
The number of dots will be variable based on the length of the two controls.
Again, this only works with a mono spaced font, such as Courier. If you use a proportional font, then you need to do it differently.
Jim.
1. Create one control with all the dots. ie. if you want a 60 character wide result, create a control with 60 dots in the control source.
2. Have control1 and control2 with the data. Control1 will contain what is on the left side, control2 will contain what is on the right. Both are left justified.
3. Hide all those controls.
4. Create one control (visible), with the control source of:
=[Control1] & Right$([ControlWithDots], 60-Len([Control1]) - Len([Control2])) & [Control2]
The number of dots will be variable based on the length of the two controls.
Again, this only works with a mono spaced font, such as Courier. If you use a proportional font, then you need to do it differently.
Jim.
ASKER
OK last question If I have a single visible control... then I will not be able to have control 1 left justified and control 2 right justified .... correct?
No because your combining them into a single control with the dots. Since the dots are variable, Control 2 will end up right justified.
Give it a go and you'll see.
Jim
Give it a go and you'll see.
Jim
ASKER
OK tried but I'm getting a syntax error on that control
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<OK tried but I'm getting a syntax error on that control>>
Worked fine here.
Jim.
Worked fine here.
Jim.
I would left justify the first field and right justify the right field. If they are too far apart, you might also want to use alternate shading for the background of the lines.