Report Design

[Malashaan]

I'm trying to lay out my report data something like the following:

Length........................25mm
Width........................120mm
Depth.........................10mm
Finish Colour.................Black

I thought this would be straight forward enough but I've got completely stumped by it. How can I control the number of '.' characters displayed based on what the data value is so that the lines stay aligned? I tried using a whole line full of '.'s and giving the text an opaque background but of course it always obscures enough full stops to fit the longest piece of data in.

If I was doing this for myself I'd just change the report layout but the guy I'm doing it for is very specific about his requirements. Any ideas?

EDIT: Just had an idea, could someone confirm this will work/if there's a better way so when I come back to this I don't fee like I'm flailing in the dark?

My idea is, rather than display the data directly in a bound object created by access I could make my own text box and programmatically construct a string with the label and data value in.

 

[Trevor G]

Are you wanting what word gives Leader dots between two fields on an Access Report if so.

Open the report in design view. place your first field in the detail section then look to add a line from the text box and then make it as big as you want, then add the next field.

Then select the line properties and you can change the Border style to Dots.

 

[Malashaan]

Unless I'm missing something that line objects can do this won't work. The key behaviour I need is that the length of the line needs to adjust itself at runtime so that it exactly fills the space between the label and the data field. Ie, something like this:

Record1:

Name.......................................Lee

Record 2:

Name..................................Jennifer

I've already settled on using a fixed width font to make the problem slightly easier. All I need now is a way to make the number of dots be a fixed number minus the length of the data.

I've tested my idea and it is possible. I can construct a nested IIf statement which creates and prints a string like this:

=IIf(Len([Name])=1,"Name . . . . . . . ." & [Name], IIf(Len([Name])=2,"Shape . . . . . . . " & [Name], MsgBox "Error, invalid name passed to report"))

That only deals with names which are 1 and 2 characters long, to do the whole job I'd need 20+ nested IIf()s which is going to get ugly and incredibly time consuming. I'd have to do this for every field on the report.

I haven't tried it out in VBA (I've been mainly programming C++ for the last few years and my VB is rusty) but I think I can simplify that with a for x=0 to constant-Len([Name]) construction but it still feels like a hack.

I can't believe there isn't a simple way to do this, it seems to be a fairly standard lay out for technical data sheets etc.

 

[Pyro]

Check out Lebans functions in his demo db here. I think it should give you something to run with.

 

[Malashaan]

Thanks for the Lebans link. It's pretty similar to what I was starting to build. It revealed to me the existence of the TextWidth method which I'd managed to totally overlook the existence of :-/

All working now to a passable level. I've noticed something a little odd which is bugging me. I've started a separate thread about it.