sdzc said:
I actually do want to capture the day as it is one of the fields we export to excel for a spreadsheet.
When using Access you should avoid making use of your tables and, at all convenient times, manage your data via queries.
Tables are used for storing data. Queries, on the other hand, allow you to select only the relevant fields, to sort your data, to format the way your data looks, and to restrict the data selected - getting the query right can help improve your database's efficiency.
In this instance you are wanting to store a calculated value in a table. Storing calculated values (without an exceptional reason) is frowned upon in database design as you are creating a
non-key dependency where one table relies on one or more fields within the same table but is not dependent upon the table's primary key. An example of a
non-key dependency is an orders table with three field called Quantity, Price, Total. Total, of course, is Quantity multiplied by Price. Were the value changed in either the Quantity or Price fields then an additional process would be required to update the Total field otherwise the order Total would add up incorrectly. In this case we turn to a query and ask it to perform our calculation. This ensures that the outcome is
always exact.
With your situation you have a date field. From a date we can extract all the individual elements (Year, Month, Day, Hour, Minute, Second) as well as using the ability to format the dates any way we want.
So, in a query we would create a calculated field that references the date field and formats it however we want.
The query can then be exported in lieu of the table with any calculations/formats you wish to add.
