Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date function 1 mth, 3 mths & 15 mth recs (Access 2000)

    I have a tblTransactions that tracks all transactions that took place between customers and case managers. The fields in tblTransaction include Transaction ID, TransDate, and CaseWorker. Every month we need to print reports that list out all transactions that will be due in 1-month , in 3 months and in 15 months. In creating these reports we only pay attention on the month portion of the TransDate ie. whether a transaction occured on dec 1 or dec 31, it will be considered as due in january (for transactions that are due in one month). We don't have a specific date each month when these reports are supposed to be generated but we usually wait till almost the end of the following month. Does any know what is the best date function that I can use to create these reports?

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

    Re: Date function 1 mth, 3 mths & 15 mth recs (Access 2000)

    From your description I gather that TransDate is in the past, and that some transactions are due in 1 month, others in 3 or 15 months. I see no field that determines whether 1, 3 or 15 months is the period to be used. Or am I confusing things?

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date function 1 mth, 3 mths & 15 mth recs (Access 2000)

    that is correct Hans, TransDateis in the past. What I'd like to get is based on today's month, which transaction is already 1 month old, which is 3-month old etc. (all in separate reports) by looking at the month in TransDate. Hope you can help.

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

    Re: Date function 1 mth, 3 mths & 15 mth recs (Access 2000)

    Create a query based on your table. Add a calculated column:

    Months: DateDiff("m",<transDate>,Date())

    You can set the criteria for this column to 1, 3 or 15. A report based on the query will display only the records whose transaction is 1, 3 or 15 months old.

    However, it is not very efficient to create three almost identical versions of the query and of the report. Instead, create just one query and one report. Leave the criteria out of the query. Create a form with an option group grpMonths with three radio buttons with Option Value 1, 3 and 15, respectively. Also put a command button cmdReport on the form, with this On Click event procedure:

    Private Sub cmdReport_Click()
    DoCmd.OpenReport "NameOfTheReport", acViewPreview, , "Months = " & Me.grpMonths
    End Sub

    The user clicks one of the options, then the command button. The report will display the appropriate records.

  5. #5
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date function 1 mth, 3 mths & 15 mth recs (Access 2000)

    I did the way you advised and it works like a charm. thank you hans.

Posting Permissions

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