Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age Date (Access 97)

    I'm currently using this formula in a query to calculate the age of a record: Age: Date()-[First_Attempt], which is being diplayed in a form.

    I would either like to freeze the age for that record when it has been closed or to not display it at all. To close a record, I've created an option group with 2 check boxes, i.e. Open (1) or Closed (2), with the numeric value being stored in a table.

    Thanks,

    Scott

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

    Re: Age Date (Access 97)

    You will also need an extra field to hold either the frozen age (you might call it IceAge <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), or the date on which you "freeze" the age. In the AfterUpdate event of the option group, set this field if the status is Closed.
    Let's say that your Open/Closed field is named Status, and that you create a new field of type Date/Time named Freeze_Date.

    If you want to display the actual or frozen date, depending on the value of Status, use

    Age:IIf([Status]=1,Date()-[First_Attempt],[Freeze_Date]-[First_Attempt])

    If you don't want to display the age at all if the record is closed, use

    Age:IIf([Status]=1,Date()-[First_Attempt],Null)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Age Date (Access 97)

    Following up on Hans' suggestion, I'd create a new field that contains the date at which you want to freeze it (otherwise it is null). Your age calculation would then look like this:
    = NZ( FreezeDate,Date( ) ) - [First_Attempt]
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age Date (Access 97)

    Hi Hans,

    Thanks for the response. Fortunately, I already had a field to store the date when the record is closed, i.e. Date_Received (although, I do like IceAge).

    I tried it this morning, but I ran into some trouble. With a few modifications to some of the field names, I input Age:IIf([Record_Status]=1,Date()-[First_Attempt],[Date_Received]-[First_Attempt]) into the AfterUpdate event for the Option group; however, when I change the status from Open to Closed of one of the records, Access returns this error message:

    "Microsoft Access can't find macro 'Age:IIf([Record_Status]=1,Date()-[First_Attempt],[Date_Received]-[First_Attempt])'."

    I'm not very fluent in expression building or expression syntax, so you'll have to bear with me. Do I need to build a macro? if 'Age:IIf([Record_Status]=1,Date()-[First_Attempt],[Date_Received]-[First_Attempt])' is the condition, then what would the action be?

    Also, how will the Age text box, whose control source is 'Age' (from the Query that the Form is bound to), know to display the "frozen age", rather than the "active" age. I assume that this is the purpose of the expression tied to the Open/Close Option Group - I just wanted to confirm this point, and that I don't need another text field to display the "frozen age". Just for the heck of it, I did add an expression to my Query, 'IceAge: [Date_Received]-[First_Attempt]', which does calculate the "frozen" date. Now if I can only get it to display on my form....

    Lastly, Mark posted a response to yours with some additional information, and I'm not sure how it fits in with what you suggested.

    Thank you again for you help. I really appreciate it.

    Best,
    Scott

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

    Re: Age Date (Access 97)

    Hi Scott,

    First, I'll try to explain how to modify the AfterUpdate event of the option group:

    Clear the AfterUpdate event of the option group.
    Click the dropdown list.
    Select Event Procedure.
    Click the Builder button (the three dots) to the right of the AfterUpdate event.
    You will be taken to the form module, with the event routine ready for you to fill. It should look like (assuming Record_Status is the name of the option group)

    Private Sub Record_Status_AfterUpdate()

    End Sub

    with the blinking cursor in the blank line in between. Type

    If [Record_Status] = 2 Then
    [Date_Received] = Date
    End If

    This means, if the status has gone to frozen, put today's date in Date_Received.
    Close the module window.

    Next, we want to display the "age". Add the age as a calculated field to the query based on the table. You already have a field Date_Received in your table, so you don't need a field Age in the table too - it would be redundant information. The calculated field in the query would look lije the expression I posted earlier:

    Age:IIf([Record_Status]=1,Date()-[First_Attempt],[Date_Received]-[First_Attempt])

    When you have saved the query and made it the record source of the form, you can put a text box on your form with the Age field as control source.

    Mark''s suggestion was to omit the Record_Status field entirely, and to use the Date_Received field only. After all, if this is filled with a date, the record is frozen, otherwise, it isn't.

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age Date (Access 97)

    Hi Hans,

    I seem to be getting warmer....but not warm enough I guess. I input the event routine, without any problems... I think....however....

    In my query, after I changed "Age's" calculated expression on the Field line from "Age: Date()-(First_Attempt]" to "Age: IIf([Record_Status]=1,Date()-[First_Attempt],[Date_Received]-[First_Attempt])", and I then run the query, the value that's now returned in the field is #Error. #Error is also returned in text field in the Form "Age" is the control source of.

    Conceptually, this all makes sense to me; I use IF functions all the time in Excel.. however, I'm puzzeled...Thank you again for your help.

    Best,
    Scott

Posting Permissions

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