time functions - displaying and calculating

[jerryczarnowski]

Hello everyone,

My problem I believe has a simple solution possibly just dealing with the format and input masks in table design but being a beginner, I am not sure how to use these tools properly to get the desired results for the scenario below:

I have a table with 3 columns named "cycle time", "lot size", and "total cycle time". How do I enter cycle time in minutes as integers and display them in time format, ex: user enters 20 and after update, the field displays as 00:20 reflecting 20 minutes. Then in the AfterUpdate event of "lot size" (integer type), the total cycle time field is updated by multiplying "cycle time" by "lot size" and displays as 1 hr 20 min (cycle time= 20 minutes x lot size=4)? It seems like Access could do this without extensive VBA or complex expressions or is it necessary? Any help is greatly appreciated.

Happy New Year, Jerry

 

[Galaxiom]

Firstly your table should not store the calculated value. It should be calculated as required for reports and queries. Read about "Normalization" for more information.

Access Date/Time fields store the number of days since 30 December 1899 for the date while the Time component is fractional parts of a day. Really it is meant to store Date and Time of Day rather than elapsed time.

If you used Date/Time you will notice your Time field is not displaying 00:20 but more like 00:20 AM

Store the CycleTime as a number of minutes.

If you need to display hours:minutes later then you can use an expression:

TotalCycle\60 & ":" & Mod(TotalCycle/60)

Note the use of the backslash which is the Integer Divide Operator.
It is equivalent to:

Int(Int(a)/Int(b))

This value will be a string so no longer can be used as a number.

I also strongly recommend you drop the spaces from all names.
Use names like CycleTime, LotSize etc (often known CamelCase)

Ultimately it saves a lot of typing when you get further advanced and makes code easier to read.

 

[jerryczarnowski]

Thanks for the quick response and clarifying the use of Date/Time. I kept cycle time and lot size as integer type and changed total cycle time to a calculated field and plugged in an expression similar to the example you provided...works great, my form shows the total exactly how I want. Now my question is did I violate good database design by having a calculated field in a table that should only contain data entry of facts? Should fancy formatting and representing of totals be shown only on a form or report? If so, where would I insert the expression as the form's record source is a sql statement...can you use expressions in sql statements?

 

[Galaxiom]

Access 2010 supports calculated fields in tables for use in web based databases but in general calculated fields are to be avoided. Best to calculate from the fundamental data each time unless the calculation is huge. (That is huge in computer terms where the computer can do millions of calculations in a moment.)

The expressions can be used in SQL.

SELECT TotalCycle\60 & ":" & Mod(TotalCycle/60) As SomeName
FROM yourtable

Or in the field cell of the Query Designer.

SomeName:[TotalCycle]\60 & ":" & Mod([TotalCycle]/60)

 

[jerryczarnowski]

Thanks again, looks like I need to explore expressions more...didn't realize how flexible they are and how many places they can be used. I will experiment with the SQL.

 

[jerryczarnowski]

Could anyone tell me the equivalent in 2007 of the following: In 2010, I have a field of data type integer called LotSize and another of the same type called CycleTime. I then have a field of calculated data type called TotalCycleTime which uses an expression and produces a result type of text. The final value is the two integer fields above calculated and formattted. Example: CycleTime = 20 and LotSize = 4 and the TotalCycleTime is 1 hr 20 min. Since calculated type fields are new in 2010 and the company I work for uses 2007, I need a quick conversion to be able to display the functionality of an application.
Any help is greatly appreciated.

Thanks, Jerry

 

[Galaxiom]

2007 does not have calculated fields.

In a normalized database the calculation would be done in the query used as the form or report's recordsource.

 

[jerryczarnowski]

If I understand, what you are suggesting is to insert the expression in a query or a sql statement that can be used as a record source for the form. In this case, I am using a sql statement as the record source when the form is loaded...should I modify the sql statement and requery? I chose the calculated field initially because the users interact often in a session by changing the LotSize for analysis and it seemed like updating a field would be more efficient than requerying the table. I can experiment with both as the database is small and requerying may not suffer that big of a hit. Thanks again, Jerry

 

[Galaxiom]

Updating a field would be dramatically slower than using the expression in the query.

You could use a reference to control on the form in the recordsource query.

 

[jerryczarnowski]

is it because updating a field performs a write to the disk as opposed to a query producing a result set in RAM? I will experiment with referencing a control on a form in the recordsource query...I'll try to google some examples unless you happen to have an example handy. Again, thanks for the help.

 

[Galaxiom]

SELECT whatever FROM sometable WHERE somefield = Forms!formname.controlname