Expressions

[Don C]

I am a new user of Access 2007. I have 2 entries on a form. [Mon In] & [Mon
Out]. They both are Medium Times with an Input Mask: Medium Time. When I
enter into these fields, I enter times that the employee started and
finishes. IE: 9:00 AM, 4:30 PM. I want to have Access calculate the amount of
hours that the employee worked (4.5) and put it in the [Total Monday] field
on my form. Also if [Mon In] is left blank I want [Total Monday] to equal 0.
What Expression do I use for this.

 

[John W. Vinson]

I am a new user of Access 2007. I have 2 entries on a form. [Mon In] & [Mon
Out]. They both are Medium Times with an Input Mask: Medium Time. When I
enter into these fields, I enter times that the employee started and
finishes. IE: 9:00 AM, 4:30 PM. I want to have Access calculate the amount of
hours that the employee worked (4.5) and put it in the [Total Monday] field
on my form. Also if [Mon In] is left blank I want [Total Monday] to equal 0.
What Expression do I use for this.

First off... STOP.

If you're starting your database design with a Form, you're starting in the
middle of the job. Your Tables are fundamental; forms are just windows, tools
to manage the data in correctly normalized tables.

If you have table fields named Mon In, Tue In, Wed In and so on... your table
structure is wrong. That's decent spreadsheet design but incorrect relational
table design. You should not have repeating fields, and should emphatically
NOT store data in fieldnames!

What in fact is the structure of your tables?


To directly answer your question though - you need the DateDiff() function.
This lets you calculate the difference between two date/time values in any
unit from seconds to years - but it calculates integer differences; so you'll
want to calculate the difference in miNutes and divide by 60 to get fraction
hours: you could set the Control Source of the [Total Monday] textbox to

=DateDiff("n", [Mon In], [Mon Out])

But *do* fix your table structure FIRST; among other things it should have one
record (not two fields in a record) per workshift, and the time in and time
out should contain both the date and the time, not just the time.

 

[Don C]

Thanks John
The formula worked well execept I had to devide the 450 that it returned by
60 to get the hours of 7.5 that I wanted. I had already added [Mon In] [Mon
Out] and [Total Monday] to my table. I am using a templete called Time Card
and modifing it. Thanks again.
--
Don C

I am a new user of Access 2007. I have 2 entries on a form. [Mon In] & [Mon
Out]. They both are Medium Times with an Input Mask: Medium Time. When I
enter into these fields, I enter times that the employee started and
finishes. IE: 9:00 AM, 4:30 PM. I want to have Access calculate the amount of
hours that the employee worked (4.5) and put it in the [Total Monday] field
on my form. Also if [Mon In] is left blank I want [Total Monday] to equal 0.
What Expression do I use for this.

First off... STOP.

If you're starting your database design with a Form, you're starting in the
middle of the job. Your Tables are fundamental; forms are just windows, tools
to manage the data in correctly normalized tables.

If you have table fields named Mon In, Tue In, Wed In and so on... your table
structure is wrong. That's decent spreadsheet design but incorrect relational
table design. You should not have repeating fields, and should emphatically
NOT store data in fieldnames!

What in fact is the structure of your tables?


To directly answer your question though - you need the DateDiff() function.
This lets you calculate the difference between two date/time values in any
unit from seconds to years - but it calculates integer differences; so you'll
want to calculate the difference in miNutes and divide by 60 to get fraction
hours: you could set the Control Source of the [Total Monday] textbox to

=DateDiff("n", [Mon In], [Mon Out])

But *do* fix your table structure FIRST; among other things it should have one
record (not two fields in a record) per workshift, and the time in and time
out should contain both the date and the time, not just the time.

 

[John W. Vinson]

Thanks John
The formula worked well execept I had to devide the 450 that it returned by
60 to get the hours of 7.5 that I wanted. I had already added [Mon In] [Mon
Out] and [Total Monday] to my table. I am using a templete called Time Card
and modifing it. Thanks again.

Sorry - meant to add about the division. You got it though!

If your template has *table fields* (not form controls) for Mon In or for
Total Monday - it's simply *WRONGLY DESIGNED*. At the very least, the [Total
Monday] field in your table is redundant. Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact.

If it's a Microsoft template, shame on them!! Could you let me know the source
of the template?

 

[Don C]

-- John,

I downloaded the template from Micro Soft called Time Card. It had a table
listed under Supporting Tables called Work Hours. I added to the table Monday
Date, Monday In, Monday Out , Total Monday, Tuesday Date, Tuesday In,
Tuesday Out , Total Tuesday, ect. The Table already had a Date Worked Field
but I needed to add the other fields so that my form works. It is a form that
I already use in my business and is easier to use so I don’t have to train a
new entry format.The Date Worked field works with the rest of the program. If
I could just make it = to Monday Date, Tuesday Date ect. on the table. The
rest of the program would work fine. Each part on the report needs to be a
new record.

Don C

Thanks John
The formula worked well execept I had to devide the 450 that it returned by
60 to get the hours of 7.5 that I wanted. I had already added [Mon In] [Mon
Out] and [Total Monday] to my table. I am using a templete called Time Card
and modifing it. Thanks again.

Sorry - meant to add about the division. You got it though!

If your template has *table fields* (not form controls) for Mon In or for
Total Monday - it's simply *WRONGLY DESIGNED*. At the very least, the [Total
Monday] field in your table is redundant. Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact.

If it's a Microsoft template, shame on them!! Could you let me know the source
of the template?

 

[John W. Vinson]

-- John,

I downloaded the template from Micro Soft called Time Card. It had a table
listed under Supporting Tables called Work Hours. I added to the table Monday
Date, Monday In, Monday Out , Total Monday, Tuesday Date, Tuesday In,
Tuesday Out , Total Tuesday, ect. The Table already had a Date Worked Field
but I needed to add the other fields so that my form works. It is a form that
I already use in my business and is easier to use so I don’t have to train a
new entry format.The Date Worked field works with the rest of the program. If
I could just make it = to Monday Date, Tuesday Date ect. on the table. The
rest of the program would work fine. Each part on the report needs to be a
new record.

Well, I'm sorry to say, but the template was correct and your added fields are
wrong, and *they are the problem*.

Designing a table to fit a (paper) form is pretty much guaranteed to give you
an incorrect data structure. It's certainly doing so in this case. The rest of
the program would *NOT* work fine; any reports or calculations summing the
hours would need to be completely rewritten, any calculations of payroll would
need to be completely rewritten, etc.

YOu can - if it's absolutely vital that no employees be subjected to the
horrors of retraining - create a form with *unbound* fields for the seven
days' values, with some fairly complex VBA code to calculate the actual
calendar date for Monday, Tuesday etc. and add a record for that date to the
table. Writing this code would go rather beyond the scope of volunteer support
on the newsgroup, at least for me. Have you in fact tried using the database
as it was originally designed, and ESTABLISHED that it is too difficult to
use?