Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I need some help with code to read a query that isolates detail records and prints a report results as follows:

    <pre>No: 1000000-H to 1000014-D (inc.) 15
    No: 1005001-D to 1005014-E (inc.) 14
    Total 29
    </pre>


    Each line should print the first and last reference number of the range with a total of reference numbers.

    The break should be on the first 7 characters of reference number excluding the dash and alpha check digit

    There may be one or many ranges of reference numbers.

    See attached db and query qry_RT_DL_Original for input file.

    Thanks, John
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I'm sorry, I don;t understand. Your example would seem to imply that you want to group on the first four or five characters, not on the first seven.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    The group is at the break in sequence (first 7 characters), each line/group should show the min and max reference in the group

    <pre>
    No: 1000000-H to 1000014-D (inc.) 15
    No: 1005001-D to 1005014-E (inc.) 14
    Total 29
    </pre>


    The input looks like the following:

    1000000-H
    1000001-C
    1000002-J
    1000003-D
    1000004-K
    1000005-E
    1000006-L
    1000007-F
    1000008-A
    1000009-G
    1000010-B
    1000011-H
    1000012-C
    1000013-J
    1000014-D
    ---------------- break in group ---------------
    1005001-D
    1005002-K
    1005003-E
    1005004-L
    1005005-F
    1005006-A
    1005007-G
    1005008-B
    1005009-H
    1005010-C
    1005011-J
    1005012-D
    1005013-K
    1005014-E
    ---------------- break in group ---------------

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have attached a solution. It is not elegant, and it will be very slow with large tables, but it works on the example you posted.
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Hans

    Thanks for the clever solution

    The code worked great in my app, however the report took 4 minutes 9you noted this) when isolating 800 records out of a table of 200,000

    I changed the first query to a make table query and pointed the other queries to the temp table and same report runs in about 15 seconds.

    Was this the best approach to speed up the report?

    My final report is fairly complex, made up of several other sub reports and your solution is a small part of the total report.

    I changed the report you provided ito a sub report on a main report,

    When I run the report and no records qualify for this particular portion, how can I get the the sub report to print on the main report as follows:

    <pre>
    No: to (inc.) 0
    Total 0
    </pre>


    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The maketable query may be your best bet.

    You can use the HasData property of the subreport to determine if it displays records, for example in the control source of a text box.
    In the report header or footer of the subreport:
    <pre>=IIf([HasData],Null,"Total 0")</pre>

    or on the main report
    <pre>=IIf([SubReportName].[Report].[HasData],Null,"Total 0")</pre>


  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Hans

    Works for me!

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •