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

Posted on 2005-03-10
Medium Priority
Last Modified: 2012-05-05

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.
Question by:pauldonson
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 41

Expert Comment

ID: 13508337
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).

Author Comment

ID: 13508356
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!
LVL 36

Expert Comment

ID: 13508950
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


Author Comment

ID: 13538480
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!!

Accepted Solution

DarthMod earned 0 total points
ID: 14104469
Submitted to PAQ with points refunded (500)

Community Support Moderator

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question