Can someone help me with "time" fields in an Access 2007 Form?

colinasad
colinasad used Ask the Experts™
on
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?

Many thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I am still being presented with the "date" part of the data when I enter my "Short Time" control, but I have discovered the reason for the strange value I reported earlier.

So, don't worry about that aspect, but why am I still seing the date part of the data rather than the time part?

Regards.
Have you tried using a "typed in" format for the date/time instead of using Short Time?
If not, try typing: 00\:00\:00 into the Format and Input Mask property of the field. (if you don't need the date part)

I tested the calculation on this and it worked.

Author

Commented:
Thanks for the suggestion GeekEnVogue, but it didn't seem to work for me.

When I specified   00\:00\:00   as my control's Format, "00:00" times were displayed as "04:01:78" and when I entered the field to edit it, the date value re-appeared.

This is really frustrating. Access and SQL Server can do very powerful things very quickly, then you get stuck for hours on a small niggly thing like this.

Regards.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Short Time is an SQL Server special.
Try the normal DateTime value which is fully compatible with Access.

/gustav
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
A value that is a date or a time is really a Double Numeric datatype.

The whole number (to the left of the decimal point) is an integer representing then number of days since 1/1/1900.

The Decimal part of the number is the Time component.
For example: .5 is 12 noon (make sense?

Technically, you cannot have a Time without a date.
In other words, you cant indicate 4 hours, 37 minutes directly in Access.
4:37 is really 4 o'clock and 37 minutes, not technically 4 hours.
As you can see AM/PM and the 24hour format complicates things.

Make your Punch in and out fields as Date Fields formatted as date and time.
(I don't really see a need for AMStart, PMStart, or OTStart?)
This can be determined by when they punch in.

To get the total time you should convert to minutes.
Total Time =DateDiff("n",StartDate,Enddate)
(1/1/2009 9:00AM to 1/1/2009 4:30 PM is 450 minutes)

Then convert the minutes to Hours and a decimal.
For example, 450 minutes is 450/60=7.5
Then convert the decimal to minutes&
.5/.01667=30

So 450 minutes is 7 hours and 30 minutes
You can separate the whole number from the decimal and display this value however you like.

So I*Always* calculate times with the full date and time, then format the date in the end.

Now even if you are using SQL, (if it were me), I would convert all the date values to Double datatypes (or whatever is the equivalent Datatype in SQL) *First*, right up front.
Then do all your calculations in this number format.
Then only when you get the final result, will you do any formatting at all.
Thus, avoiding all of this confusion.

Lets see what some other Experts have to say, in case I am not understanding something&

JeffCoachman

Sorry it is not working for you. Leave off the last "\:00" for seconds.
I know exactly what you mean about the "niggly" things. I have spend hours finding an answer to one little thing. But, that is how we learn!

Author

Commented:
Thanks for all the input, folks.

I appreciate that if I want to use the inbuilt VBA "time" functions then I need to use a "datetime" or "smalldatetime" variable which are a composite of a date and time.
(Seems a bit heavy-handed to someone who has spent the past 30 years scrimping on storage and memory space, but there we are.)

This particular Access Form is showing an employee's times for a particular day.
I display the date at the top of the form (the manager has already specified this before the form is displayed) and I just want to display the times of the employee's various clocking actions for that day (even though their full version also includes a date component).

What I don't understand is why my Access Form's control  initially correctly displays only the "time" element when I set the format as "Short Time" but when I go into the control to edit the value it reverts to the "date" part, even though I have specified a time "00:00" input mask on the control too.

I was hoping that these control properties would allow me to focus on the "time" element of the data, avoiding the need for a lot of VBA code behind the scenes, separating the components before editing and having to put them all back together again after editing.

Regards.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> when I go into the control to edit the value it reverts to the "date" part

That's because otherwise if you entered, say, 10:14, it would save that with the date part set to 1899-12-30 which probably is not what you want.

You can do this:

1. Make the TextBox unbound.
2. At OnCurrent of the form, set the TextBox to the time part of the datetime field.
3. At OnChange of the TextBox, write some value to some field of the record to set Dirty to True.
4. At AfterUpdate of the form write the time to the datetime field:
  Me!DateTimeField.Value = Int(Me!DateTimeField.Value) + TimeValue(Me!txtDateTime.Value)
5. Set Me.Dirty = False to save it again.

Sometimes you can use BeforeUpdate at step 4 and skip step 5 but not in this case if I recall correctly.

/gustav
MIS Liason
Most Valuable Expert 2012
Commented:
<What I don't understand is why my Access Form's control  initially correctly displays only the "time" element when I set the format as "Short Time" but when I go into the control to edit the value it reverts to the "date" part, even though I have specified a time "00:00" input mask on the control too.>

That was the whole point of my post:
1. *Always* store the Full date and time.
2. *Never* use input masks for dates, they are more trouble than they are worth.
3. Don't create your owwn custom formats
4. (Optional) Again, if you want to avoid any chances for error or confusion with SQL Date datatypes and Access date datatypes, convert the Date/times to Double Data (Decimal in SQL) and do all your math directly, then format the values in the end.

I am not saying that you should not take help from other Experts.
What I am saying is that I always keep things simple and direct, and I never have any of the issues you are facing.

JeffCoachman
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
4. (Optional) Again, if you want to avoid any chances for error or confusion with SQL Date datatypes and Access date datatypes, convert the Date/times to Double Data (Decimal in SQL) and do all your math directly, then format the values in the end.

Jeff, this really shouldn't be done, actually is never needed as long as you stick to the build in functions to handle date/time.
The exception is when you wish to move beyond the intended use of the date/time data type of Access like handling milliseconds correctly. This is explained in my article here:

  http://www.devx.com/dbzone/Article/39046 

> .. always keep things simple and direct
True. For example, elapsed time within 24 hours can be calculated directly if you like:

  datTimeElapsed = datTimeEnd - datTimeStart

and then formatted as, say, hh:nn:ss.

/gustav

Author

Commented:
Hope you don't mind me splitting the points.
I guess I was just hoping that the in-built properties of the Access Form controls would completely handle a manipulation of the time component of a datetime field, without me having to do too much (any !) VBA coding.
I eventually made my "time" controls unbound. I then separated the time component of my data, manipulated it via the unbound control (with the "Short Time" format and input mask) and re-established my datetime data after any updates.
I'm still learning what Access handles completely and what requires a little bit of creativity.
Thanks for pointing me in the right direction.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
cactus_data is a top expert here and I have learned a lot from him.

I have no problem with a points split.

My point was just a call to: "Keep it simple".
There are tons of questions here where askers get twisted into knots because of date/time issues:
For example:
What is a month: 4 weeks, 30 days, the exact number of days?
What is a week, 7 days, 5 days, seven day interval Sun-Sat, Just a seven day interval.
Times formatted as text.
Input masks conflicting with the formats
calculations crossing 24hr periods
Leap Years.
Times versus durations:
   Is "8:20" eight minutes and twenty seconds, 8:20AM, 8:20PM, Eight *hours* and twenty *Minutes*?
Number/ time conversion accuracy. .0167, .01667, .016667...?
MM-DD-YYYY, M-D-YY, DD-MM-YYYY, YYYY-MM-DD, ...etc
Times with milliseconds
...The list goes on and on.
;-)

Again, I have no problem with a points split.

Even if you had given cactus_data all the points for correctly answering your question as asked, I would not have had an issue.
;-)

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Jeff, this really shouldn't be done,>
Yes, I agree, you are correct, my point was that many of the built in date/time functions are baffling to some people new to Access.
(as they were to me)
:-)

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I don't mind the split.

As cactus_data stated, converting to numbers may be overkill.

Again, I only brought it up because manupulating Dates and Times in Access can be tricky, and perhaps using all numbers might help uou out to some extent.

JeffCoachman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial