Results 1 to 7 of 7

Thread: Date in Report

  1. #1
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Cool Unanswered: Date in Report

    I have a table containing 4 columns MDS, Quarterly1, Quarterly2, Quarterly3

    I need the Report to include only the one column date that is in the future.

    Example IF Today were August 3, the report would show the 9/2/2004 date

    MDS 1/2/2004
    Quarterly1 3/2/2004
    Quarterly2 6/2/2004
    Quarterly3 9/2/2004

    This is used to invite people to meetings on a quarterly basis.

    I cannot just use the Quarterly3 date as when the cycle starts again the future date would be MDS and the other three blank.

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    real quick:
    not sure i understand your idea of 'quarterly'. for me the year in quarters is:
    1st qtr: jan-mar (1-3)
    2nd qtr: apr-may (4-6)
    3rd qtr: jun-aug (7-9)
    4th qtr: sep-dec (10-12).
    you can't use a qry with criteria = 4th qtr?
    w

  3. #3
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Clarification - Date in Report

    Assessments are done every 90 days on each person in our building. A larger assessment is done Annually which is called an MDS. The term quarterly refers to a smaller assessment done every 90 days until a new Annual MDS is done. That's why the columns are named as they are.

    For this purpose it is the dates that matter as they are the closing date for the assessment and also the date I use for the meeting and do not reflect a quarterly time period. They also don't correspond with the calendar quarters, Just 90 day segements of time based on when the person arrives in our building.

    I enter each date into the appropriate column on the table as I schedule the assessment. I just need to get the report to identify and use the date that would be greater than the current date and choose that one (out of the 4) to put on the report, that is actually an invitation to the meeting.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Perplexed,

    Though I myself haven't used it, there have been many on here that have used the DateAdd function which can give you periods of time in dates ahead or behind the current or stated date. Either check in the forum here or in the Access online help files.

    Have a nice one,
    Bud

  5. #5
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79
    Thank you for your assistance. I appreciate your efforts

  6. #6
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    hi. i was fooling around with some date code and came up with this:

    Private Sub DateTest()

    Dim Today As Date
    Today = Now

    Select Case Today
    Case Today > #1/2/2004#
    Debug.Print "3/2/2004"
    Case Today > #3/2/2004#
    Debug.Print "6/2/2004"
    Case Today > #6/2/2004#
    Debug.Print "9/2/2004"
    Case Else
    Debug.Print "1/2/2004"
    End Select

    End Sub

    when you run it the result will come up in the immediate window. you'll have to play with it to get it to output where and the way you want. is this what you're after?

    w

  7. #7
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79
    Looks like just what I am after, Thank you.

Posting Permissions

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