Calculate based on date and time

[Gilbertjoe]

Hi. Access 2002 Question here. Here goes...
I have a form based on a single table that a user inputs a log of projects that are being worked on.
Fields are as follows:
Date (DD-MM-YY format)
Start time (HH-MM-AM/PM format)
Stop time (HH-MM-AM/PM format)
Project (TEXT Format))
Hours (Number Format)
Overtime (Number Format)


I would like to have the form autocalculate hours and then overtime if hours worked during any given date total more than 8.
Is this possible and if so, how?

 

[Edski]

For the Hours field, use the DateDiff function, with hours (h) as the interval. For example, Hours = DateDiff(h, [Start time], [Stop time]).

For the Overtime field, use = IIf([Hours]>8, [Hours]-8, 0).

Put these expression in the Control Source for the fields.

 

[Edski]

For your project field I would convert it to Number (Integer) and put the corresponding Text labels in a separate 'lookup' table. Then use a comboBox in the form.

 

[Gilbertjoe]

I have the project in a lookup now.
The formulas posted won't work because they are cumulative formulas (after the first 8 hours is reached everything entered in after that will be in the overtime field as there is no reference to a particular date). I need the formulas to work for one give date at a time. The same day can be entered numerous times on the form.
Example of how I need to have the hours and overtime fields calculate (i left project field out because it doesn't matter):
Date Start Stop Hours Overtime
1 jan 8:00 AM 10:00 AM 2
1 jan 12:00 PM 3:00 PM 3
2 jan 4:00 AM 9:00 AM 5
2 jan 3:00 PM 5:00 PM 2
2 jan 6:00 PM 9:00 PM 1 2
2 jan 10:00 PM 11:00 PM 1
3 jan 5:00 AM 8:00 AM 3
3 jan 9:00 AM 2:00 PM 5
3 jan 2:00 PM 4:00 PM 2
Notice how Hours and overtime are calculated for anything over 8 hours for only one particular date. That's what I'm trying to do. Can this be done?

 

[MakeItSo]

I'd say, create a query or just a recordset within your form code, that uses the sum-function for your hours and the GROUP BY YourDate.
I do not know your table/form names, neither your Table-Fields a.s.o., so I can't paste the complete query expression here. But I hope you know what I'm trying to say.

Based on that query/Recordset you have then the total amount of hours per day. If you add a DoCmd.Requery at the correct position, you will additionally be able to update these sums anytime...

OK?
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Gilbertjoe]

I'm not sure how to do this. Can you show me some example code?

 

[MakeItSo]

Well here's an SQL I am using as part of an Issues database:

SELECT Tab_Bugs.Category, Count(Tab_Bugs.ID) AS [Count of ID]
FROM Tab_Bugs
GROUP BY Tab_Bugs.Category;

The result here is a sum over all entries per category.

1) As Date is a reserved word in Access, better rename your date field to something like "DateRecorded" or "WorkDate" to avoid side effects.

2) So, what you'd need would be something like

SELECT YourTable.WorkDate, Count(YourTable.[Hours]) AS [Count of Hours]
FROM YourTable.
GROUP BY YourTable.WorkDate;

Hope that helps you,
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Gilbertjoe]

I have to calculate the hours based on tenths of an hour (1 hr 30 mins = 1.5).
How do I round up anything in hundreths greater that 5
(1.35 rounds to 1.4 and 1.34 to 1.3)

 

[MakeItSo]

Hi Gilbert,

you could simply do this: hours=CDbl(CInt(hours*10)/10).
This will first multiply your value by 10 and round that up or down (13,4 -> 13; 13,5 -> 14). By dividing it back by 10, you have precision to the tenth...
;-)

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Gilbertjoe]

I think I found another answer to rounding and truncating numbers also in the Microsoft Knowledge base article 97524 which I have pasted below. I'll add to this thread once I try it and let you know if it works.


This article was previously published under Q97524
Moderate: Requires basic macro, coding, and interoperability skills.


SUMMARY
The Format property of a control can round a Number or Currency field to the number of decimal places that you want. However, this does not change the underlying data, which may contain additional digits that the control does not display. If you add the values in this control, the sum is based on the actual values and not on the displayed values. This may make the total seem inaccurate.

This article shows you how to create four user-defined functions to round or truncate data to two decimal places so that the displayed and formatted value and the actual numeric or currency data are the same.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0
MORE INFORMATION
The functions are presented in two styles. The first style is appropriate for the AfterUpdate property of a form control to ensure that the data entered matches the data that is displayed. The second style is for use in expressions and calculated controls.

To round or truncate numbers to two decimal places, create a new module and add the following functions.
'******************************************************
' Declarations section of the module
'******************************************************

Option Explicit
Const Factor = 100

'=====================================================
' RoundAU and TruncAU are designed to be added to the
' AfterUpdate property on a form control.
'=====================================================
Function RoundAU(X As Control)

X = Int(X * Factor + .5) / Factor

End Function

Function TruncAU(X As Control)
X = Int(X * Factor) / Factor
End Function

'=====================================================
' RoundCC and TruncCC are designed to be used in
' expressions and calculated controls on forms and reports.
'=====================================================
Function RoundCC(X)

RoundCC = Int (X * Factor + 0.5) / Factor

End Function

Function TruncCC(X)
TruncCC = Int (X * Factor) / Factor
End Function

Examples of Using the Round and Truncate Functions
The following examples use the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier).

CAUTION: Following the steps in these examples will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.
Example 1
Use the TruncAU() function to the AfterUpdate property of a form:
Open the sample database Northwind.mdb.
Create a new module called Rounding, and type the procedures in the preceding section.
Open the Products form in Design view, and add the TruncAU() function to the AfterUpdate property of the UnitPrice field (or Unit Price field in version 2.0 or earlier): Form: Products
--------------
Control Name: Unit Price
AfterUpdate: =TruncAU([UnitPrice])

If a user accidentally enters $23.055 instead of $23.05, the TruncAu() function catches the mistake and changes the value to $23.05. If you use the RoundAu() function instead, the function changes the value to $23.06. If you use neither function, the value is displayed as $23.06, but the entered value, $23.055, is used in any calculations.
Example 2
Use the RoundCC() function with an expression in a report's group footer. This example assumes that you have already created the Rounding module in step 2 of Example 1:
Open the sample database Northwind.mdb.
Open the Summary Of Sales By Year report in Design view and use the RoundCC() function in the ControlSource property of two controls in the report's group footer:
In Microsoft Access 7.0 and 97:

Report: Summary of Sales By Year
--------------------------------
Control Name: QuarterSales
ControlSource: =Sum(RoundCC([SubTotal]))

Control Name: YearTotal
ControlSource: =Sum(RoundCC([SubTotal]))

In Microsoft Access 1.x and 2.0:

Report: Summary of Sales By Year
--------------------------------
Control Name: Total Sales for Quarter
ControlSource: =Sum(RoundCC([Order Amount]))

Control Name: Total Sales for Year
ControlSource: =Sum(RoundCC([Order Amount]))

If you use RoundCC(), the report sums the values displayed in the report, even though the actual values may contain hidden digits.
NOTE: To change the number of decimal places that the functions use, open the Rounding module in Design view and change the value of the global constant, Factor, as follows: 10 = 1 decimal place
100 = 2 decimal places
1000 = 3 decimal places, and so on

Limitations
These functions should only be used with Currency data. If used with Double or Single numbers, you may still receive minor rounding errors. The reason for this is that Single and Double numbers are floating point. They cannot store an exact binary representation of decimal fractions. Therefore there will always be some error. However, Currency values are scaled integers and can store an exact binary representation of fractions to 4 decimal places

 

[Gilbertjoe]

Well I've tried it and it seems to be working perfectly.