not certain I can explain this but please read

[cyberbiker]

I posted this to the VBA forum in error so appoligies for a duplicate post.
Please bear with me. I may be dense today but I have an interesting problem and will try to describe it.

I need to create a report from an existing table consisting of 2 fields.

Field one is a text field containing an Index number
field 2 is a text field containing a "title"
The index numbers are significant both by number and position.
They are entered into this table like this: (examples)
1
10
100
102
100-000
2
20
200
213
213-011 etc, etc, etc,
What I need to do is to print a report that looks like this:
1 Fasteners
10 Bolts
100 metric,fine thread
101 metric, course thread
101-001 metric, coarse thread, stainless
101-002 metric coarse thread, grade 5
11 screws
110 stainless
111 wood
2 glue
20 for paper
21 for wood
210 waterproof
211 non waterproof
And so on and so on for 2000 or so entries.

The report must be done in ACCESS 97, I cannot modify existing tables in any way. Level 1, 2, 3, etc need to be larger and bold font, levels 10, 20, 30 etc need to be smaller and bold, levels 1xx, 2xx etc again semibold and
1xx-xxx etc entries need to be regular font.

I thought I had a solution using nested sub reports but I can only use 2 "nested" sub reports in ACCESS 97 and I need to be able to use 3 to do things my way.
I have tried parsing out the index number field and creating a temp table then creating a report but for some reason, I cannot seem to reason out a way to get what I want.
I may be just having a mental block from frustration, but I really need help.

Did I mention, they want it today?


Terry (cyberbiker)
Terry (cyberbiker)

 

[corsair2]

Hi

I know this may be a bit daft but how about trying a mail-merge into Word and then set up the document using Words "document map view" which will allow the various formats you require?
I realize you probably have too much data in the table but if all else fails..

Best of luck

 

[cyberbiker]

Well, I do not use really know much of word, but I will look at it. Is there a limit on the number of records I could do this with? Or just the size of the file? Terry (cyberbiker)

 

[cyberbiker]

I do not think I can use word to do what I want. I looked at the mail merge and either I do not understand what was meant or was not clear on what I need.
First I need to set it up so the user basically clicks on a desktop icon and the report "pops out".
Any other ideas would really be appreciated. Terry (cyberbiker)

 

[zambezibill]

Have you looked at VBA's GroupBy, GroupOn, GroupInterval methods?
I don't remember if they were a functionality of A97, but if so, will be what you are looking for.

Cheers, Bill

 

[experienced]

This is how I would do it.

1. First, create a query using this table. The first 2 columns are the same as those in the table. The next 2 columns should be

NumLen: Len(strIndexNum)
LeaderLen: IIF(NumLen=1, 0, IIF(NumLen=2, 2, IIF(NumLen=3, 4, 6)))

where strIndexNum is the field name of the index number. I used 0, 2, 4, and 6 as the leading spaces, but you can use any numbers you like.

2. Use this query as the record source for the report.

3. In Details Section, create a control and call it strIndexLine. You should have the invisible controls for the index number, title, and LeaderLen in the same section. Assign the concatenated value to strIndexLine like:

strIndexLine = Space$(LeaderLen) & strIndexNum & " " & strTitle

LeaderLen will give the appropriate leading space for each line.

I haven't tested this, so you may need to tweak a bit.

I think something like this will work. However, a much better way is to redesign the table and add the field(s) to indicate the level of each line.

Cheers

 

[jimmythegeek]

Check the Access Modules (VBA Forum) for a solution. It helps us if you mention that you cross posted this question in another forum. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[cyberbiker]

Thanks for the advice re: protocol Jimmy (and the helpful post in the VBA forum). I have had a rough few days and am becoming more than a bit "goofy".
And Thanks to "experienced" also.
Redesigning the origional table was what I wanted to do but "That is not an option".
The company I work for is pretty good in most ways, but arbitrary constraints are put on what I do by higher ups who have only a little knowlege of computers.
This report is another example of that. Terry (cyberbiker)