Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Report - Reference Detail (2000)

    Here's the background - hopefully I'm clear in my requirements. As always, please ask for further detail, if I've not provided enough.

    My Access 2000 Database is made up, from two linked Oracle tables. From these tables, I've created a query that sorts by licence number. This query, is then used as the basis for a report that will be run monthly, to produce account statements for mailing.

    Below, is the DETAIL section of the report, for one "Licence Number"

    <table border=1><td>Bill Number</td><td>Bill Date</td><td>Description</td><td>Amount</td><td>16699</td><td>24-Nov-04</td><td>Food Premise Fee</td><td>$100.00</td><td>17299</td><td>31-Jan-05</td><td>Service Charge</td><td>$2.00</td><td>18743</td><td>28-Feb-05</td><td>Service Charge</td><td>$2.00</td></table>

    After the detail section, is the Licence Footer. In the licence footer, I'd like to place a statement that will change, depending on the number of days past due an account is - based on the first line of detail.

    For example, Bill Number 16699 was DUE 31-DEC-04. On 31-Jan-05, I want to print it is 1 month past due. On Feb 28, I want to report it is 2 months past due.

    The 2nd, 3rd, etc.. lines are not referenced in the past due requirements.

    Any ideas?? Thanks for your help!
    Christopher Baldrey

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    Chris,

    You could use a DMin function to retrieve the first Bill Date for the current Licence Number and subtract it from the current date in Months.
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    Thanks Francois - I'll attempt that function.

    So far, I'm not quite sure how it works, but I'll keep trying, and of course, use the online help.
    Christopher Baldrey

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Report - Reference Detail (2000)

    >>For example, Bill Number 16699 was DUE 31-DEC-04. On 31-Jan-05, I want to print it is 1 month past due. On Feb 28, I want to report it is 2 months past due.
    <<
    What is the definition of DUE, is it applicable one month past the Bill Date? Your question would then presumably be what bills are OVERDUE, is that due one month after the Bill Date? Is this calc then calculated based upon the last day of the month?

    You could setup a SubReport to show these bills, this would be placed in the Licence footer.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    Put a textbox in the Licence Number Footer. Name it Overdue
    In the on Format of the Group footer, put the following code :
    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    Dim FirstDate As Date
    FirstDate = DMin("Bill Date", "YourReportQuery", "[LicenceNumber] = '" & Me.LicenceNumber & "'")
    Me.Overdue = Format(Date - FirstDate, "m")
    End Sub
    Replace the names with your control names.
    Maybe you'll have to play with Date to get the right number of months as 24-nov-04 is the bill date and not the due date.
    Francois

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    <hr>>>For example, Bill Number 16699 was DUE 31-DEC-04. On 31-Jan-05, I want to print it is 1 month past due. On Feb 28, I want to report it is 2 months past due.
    <<
    What is the definition of DUE, is it applicable one month past the Bill Date? Your question would then presumably be what bills are OVERDUE, is that due one month after the Bill Date? Is this calc then calculated based upon the last day of the month?

    You could setup a SubReport to show these bills, this would be placed in the Licence footer.<hr>
    Due is actually "upon receipt". Service charges are then calculated 30 days past "due".

    I see Francois has posted something further, so I'm off to give that a shot first .

    I've never worked with a SubReport before. I may try that option, if the other suggestion doesn't pan out.

    Thanks for everyone's help thus far.
    Christopher Baldrey

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    Francois,

    What does the Me represent in your provided code? eg. Me.LicenceNumber or Me.Overdue ???
    Christopher Baldrey

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    As this code is running in the report, the Me. represent the report itself.
    It would be the same if you use Reports.YourReportName.Overdue and Reports.YourReportName.LicenceNumber.
    In my sample, on the report you should have a textbox named Overdue and a texbox named LicenceNumber.
    If your textboxes have a different name, just type Me. and you should find the textbox names in the list.
    Hope this is clear, if not post back
    Francois

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report - Reference Detail (2000)

    Francois and Pat, thank you for your help.

    However, I haven't used these suggested methods. Our Data Architect modified the views I'm using to provide the past due values. Doing this, I've modified my query, and the report was another easy modification.

    Certainly lots of ways to do things with databases - it's just a matter of finding the right one, at the right time.
    Christopher Baldrey

Posting Permissions

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