You'll use Format() to display dates, but don't use it when you must use the results in mathematical equations. You just set the date field's Sort option or add an ORDER BY clause to the SQL statement. If you're trying to sort by a single date component—the day of the month, the month, or the year—use the Day(), Month(), or Year() functions.
For instance, if you wanted to find all the January dates, you'd use the SQL WHERE clause in the form: None of the Access date functions returns the first or the last day of a specific or relative month.
For instance, you might expect the following expression to find dates that fall after December 12, 2006: However, the resulting recordset will include December 12, 2006, entries when the entry includes a time value.
Conversely, Access won't return December 12, 2006, dates if the entry stores just a date value.
For instance, the following expression will not return the number of days between two dates: Instead, it returns -4.9825610363727E-04.
That's because Access evaluates the dates mathematically: 3 divided by 3 divided by 2007, subtract 2, and so on.
To display a single component, use the appropriate format code.