Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: get aging from a open date to the current date

    hi:

    I have a database where I have a field [open date] and a field [close date], I have another field [aging]. Im using a form to capture the information, I need to get the following: when i enter [open date] I need to calculate aging based on the current date Vs the open date, that's mean 0, in the next day aging must be 1, and in that way 2 for the next day, 3 for the next..... but when I enter data on close date, I need to have aging always calculated betwen close date and open date.

    I hope it is clear what I need help on.

    thank you....

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would think in your calculation you could use the Nz() function:

    Nz([close date], Date())
    Paul

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Paul, thank you for you input. What I nee at this point is how to update autamatically a file aging calculating it with the current date and the open date.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do not store a computed value in your database if you can avoid it. Compute the Aging data on the fly instead. If you really want to keep this data stored in a table, run an UPDATE query on a regular basis (e.g. each time the database is open) to re-compute the values.
    Have a nice day!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree; don't store the aging, just calculate/show it on reports.
    Paul

  6. #6
    Join Date
    Sep 2009
    Posts
    4
    Thank you for your comments, I just did that, I'm calculating the value every time I update the data. Running a update query. Thank you for your advices, actually now I'm remembering somethig I read about calculated fields where it recomends to do not store this kind of data, instead calculate to show on reports.

  7. #7
    Join Date
    Sep 2009
    Posts
    4
    By the way, how can I run an event (ej. update query) on regulra basis? Do I need to create a splash screen and run a query on close event? Any other recomendation?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What we're saying is not to store the value. No update query to change it. No field in the table for it. Just calculate it when you need to display it on forms or reports.
    Paul

Posting Permissions

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