Problem with short time format in Access Project and datetime fields in SQL Server
Posted on 2005-03-10
I have an Access Project (2002 format) connected to SQL Server 2000. This has been recently converted from a standalone Access mdb file.
One of the functions of the project is for entering timesheets, I have three fields:
Mon_start, Mon_end, Mon_break - These are displayed on the Access form as Short Time but they are stored in SQL Server as Datetime (because SQL doesn't have a Time field format)
The following VBA calculation would calculate the correct number of hours:
Me!TMP_Mon_hours = ([TMP_Mon_end] - [TMP_Mon_start] - [TMP_Mon_break]) * 24
(The TMP_ prefix is the name of the field on the form as the form is unbound, the defaults are populated with data from SQL when the form is loaded)
This works fine until the user overtypes any of the fields (which is allowed)
I have noticed that when the fields are populated with the defaults from the SQL Server although they display the time, when you click in the field the times are prefixed with "30/12/1899" - which I can live with except that when the user overtypes, they don't put this bit in so I get weird results from the number of hours calculations.
Can anyone suggest a way of either:
a) Preventing the fields being populated with the date prefix in the first place (easiest solution I would think); or
b) Amending the VBA code to somehow check if the date prefix is there and either add or remove it accordingly (I would prefer to avoid this solution as the timesheet calculations are VERY long!!!)
Thanks in advance.