I am developing an Access 2007 "project" (.ADP) as a front-end to a SQL Server 2005 Express database.
In the database I have a Table containing the clocking-in and clocking-out times for each employee on each day of the year. There are a few "times" for each day, eg "AM Start", "AM End", "PM Start", "PM End", "Overtime Start", "Overtime End" etc.
By doing some arithmetic with these fields I should be able to calculate the total time worked each day, and the number of overtime hours etc.
Originally I was storing these values as "smallint" in the SQL database because they would only every be between "00:00" and "23:59". My Access Form could display them as "Short Times" OK and I could even apply a "00:00" input mask to them, however the VBA date (and time) functions only seem to like dealing with "datetime" or "smalldatetime" data types and would not do the calculations properly.
When I changed the database field types to "smalldatetime" (and re-filled them appropriately) the calculations worked OK, but on my Access Form I am seeing the "00:00" values until I try to edit the field when the value changes to something like "30/12/1899" and the input mask seems to be ignored. Checking the data in the SQL Server table, the value displayed is "17/08/2009 00:00:00" as it should be.
Can anyone explain why my "Short Time" Access field displays this odd date (which is beyond the valid range for a "smalldatetime") when I try to edit it on a Form, despite having an input mask specified?