If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expressions
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. -- Don C |
#2
|
|||
|
|||
Expressions
On Sat, 24 Jan 2009 14:45:00 -0800, Don C
wrote: 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 [MVP] |
#3
|
|||
|
|||
Expressions
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 "John W. Vinson" wrote: On Sat, 24 Jan 2009 14:45:00 -0800, Don C wrote: 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 [MVP] |
#4
|
|||
|
|||
Expressions
On Sat, 24 Jan 2009 17:04:01 -0800, Don C
wrote: 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 [MVP] |
#5
|
|||
|
|||
Expressions
-- 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 "John W. Vinson" wrote: On Sat, 24 Jan 2009 17:04:01 -0800, Don C wrote: 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 [MVP] |
#6
|
|||
|
|||
Expressions
On Sat, 24 Jan 2009 22:01:00 -0800, Don C
wrote: -- 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? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|