This quick 80 second video simply shows you how to set data types in Access and this example is the Date/Time data type. It’s not that difficult to create but just in case you weren’t sure – it’s below here for your information before I explain a little background to the Date/Time data type.
Data Types In Access Databases: Knowing More About Date/Time
How are dates stored in Microsoft Access? Knowing more about data types in Access will give better control over how you handle different values in a field especially when calculating with them.
With Dates (and Time), Access stores a date in the format we use and understand but in fact Microsoft Access uses a simple incremental unique value starting with a 1 representing 31st December 1899. Therefore, this week we had a leap year date (29th February 2012) which equates to 40,968 days that have lapsed since the 31st December 1899.
So in essence, you now calculate with the underlying values and subtracting two dates is the same as subtracting two values showing the balance of days between to dates.
With Microsoft Access, you can actually store dates from the 1st January 100 (yes, year 100) which has a unique value of -657,434 to 31st December 9999!
Tags: Access Databases, creating date fields in access, data types in access, datetime data types, microsoft access data types, ms access date/time fields
If dates are stored with a unique value where 1 = 1 day then time is a fraction of 1. For example from midnight to noon which is 12 hours = half a day = 0.5 and therefore you can calculate with time too. Just use the division or multiplication of 24 (hours) when calculating in a query, form or report.