[keith10456]

MS Access 2K: "Date" Expressions

I'm attempting to calculate the "DestructionDate" of the Boxes in my database.

In one table ("RetentionSchedule"), i have the list of codes that can be assigned to a box. The table has a "RecordCode" field that contains the codes and another field for "YearsOnSite" - which states the number of years to keep the box (2, 7, 11, etc.).

In my "StorageBoxes" table (which list the box info) I have the "RecordCode" field (to assign a code to a box) and a "DateReceived" field. The DateReceived is the date that the box was received into storage.

More or less, using the DateReceived and the RecordCode fields, I need the query to calculate the date each respective box should be destroyed.

[OBP]

What are you going to do with the result of the query?

[OBP]

Basically the query needs both tables joined by the RecodCode and an expression field (Column). The expression would need to be something like DestructionDate = DateReceived + (YearsOnSite*365).
Unless you are worried about leap years of course.
If you need more help can you post a zipped copy of the database on here.

[OBP]

Is this for ISO 2002?

[cristobal03]

Something to remember when working with Date data types in Access:

Access stores dates as referential instances in time, the reference point being some day in the 19th century, I think. It doesn't really matter when the reference point is, though; dates before the reference point are negative double floating-point numbers, and dates after the reference point are positive double floating-points. One day is one integer increment--so one day = 1.000000. Therefore, when you're trying to calculate dates in Access, you're basically working with floating-point (read: decimal) numbers. Using OBP's example, consider the following scenario:

• Let Access's Date reference point = January 1, 2000

• Let DateReceived = January 1, 2001

• Let YearsOnSite = 7

In this setup, DateReceived would equal 365.24, the floating-point representation of the number of days in a year. So then, DestructionDate would be = 365.24 + (7 * 365.24), or about 2922, NOT January 1, 2008. However, it works out the same because 2922 is the number of days from the reference point that represents 8 years' time.

I hope that example wasn't too confusing. The point is, Access's Date data type is not designed to handle durations of time very well, because the starting Date is not the reference point. Durations of time must be calculated as the difference between two instances with respect to a static reference point. But all those calculations are floating-point number calculations.

chris.

[keith10456]

ODP:
I'm going to use the results of the query to create a report and make additional queries. For example, a query that will show me which boxes should have been destroyed already - based on the current date.

I don't know what "ISO 2002" is.

I used the expression you provided and it worked like a charm.


cristobal03
Thanks for the explanation... I needed that.

[cristobal03]

[bump]

A great Microsoft Knowledge Base article for future readers: Functions for calculating and for displaying Date/Time values in Access.

Just came across that and thought I'd toss that in here too.

chris.

[edit]
ISO 9002 refers to an international standard for quality systems/record-keeping (the English translation of ISO is International Organization for Standardization, though I think the actual name is French). I don't know the specifics of the standard, but OBP probably saw your values for record archival duration and assumed you designed this DB for ISO compliance. Anyway.
[/edit]