Problem with short time format in Access Project and datetime fields in SQL Server


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.
Who is Participating?
DarthModConnect With a Mentor Commented:
Submitted to PAQ with points refunded (500)

Community Support Moderator
I suppose you could remove the default value from the SQL Server side and set the textbox's default value to =Time() - that should prevent the date value from being added although this shouldn't make a difference, since times as stored as offsets from a 0 date (in this case 31/12/1899).
pauldonsonAuthor Commented:
The problem arises because the TMP fields on the form have their default value set to a field from a SQL table, hence the date being populated as well.

I have noticed, however, that even if I overtype all three fields I get strange results - worked perfectly when it was an MDB!
you can't really do what you want as sql server -always- puts a date in a date field, if one is not supplied it will add the root date in 1899

your option is to convert the data type to varchar and then convert back to time in vba

see here
pauldonsonAuthor Commented:
Thanks for the suggestion, however I really wanted to avoid changing the field type as it could have unknown knock-on effects elsewhere in the database.

After a lot of faffing about I have found that if I populate the fields using VBA (instead of using the default value field property)

TMP_Mon_start = format ([Mon_Start], "Short Time") - this solves the problem of the 31/12/1899 date appearing in the field.

With the calculations I have found that using the function "TimeValue" they work as before, eg:

Me!TMP_Mon_hours = (TimeValue([TMP_Mon_end]) - TimeValue([TMP_Mon_start]) - TimeValue([TMP_Mon_break])) * 24

Thanks again for the suggestions though!!
All Courses

From novice to tech pro — start learning today.