Calculate Hours and minutes - but not! (1 Viewer)

[Steve C]

Field1 is a number representing seconds.

After multiplying Feild1, I want my result to look like, say;

2 hours 20 minutes

I imagine Field2 counts number of times the result gets to 3600 for Hours and Field3 counts the remaining number times the result reaches 60, (I'll ignore the remainder)

Then concatenating

= (+[Field2] & " hours ") & [Field3] & " minutes"

Of course, I need the formula for Fields 2 & 3

I had a look at date/time functions, but Field1 isn't a function of any time of a a day so DateDiff DateAdd don't seem to fit.

Please could you help with this?

 

[plog]

Huh? You mentioned multiplying Field1, however you didn't say by what. Then you said you 'imagined' Field2 counts something--you don't know for sure? You've baffled me as to how you are storing this data. Can you post some sample data and then what you want as a result from this sample data?

 

[Steve C]

Sorry Plog. Thanks for answering. Didn't want to witter on.

Table1 has a PK field (autonumber of course) and a Number field which holds a number representing seconds. There is only 1 record in Table1.

Table2 has a FK related to Table1 so that all records in Table 2 have the number of seconds in Table1.

Query1 counts selected Records in Table2

Feild1
Count: ID
Query1
Count

I want to know the sum of all the Seconds. Of course, I get a perfect result in minutes with;

Field2
Result: ([Count]*[Seconds]/60)
BLANK LINE
Expression

But it's in minutes - and not ideal.

Whenever I search using time or date related words, I end up with DateAdd, DateDiff functions - which isn't right either.

Thanks again for taking the time Plog - if you might point me in the right direction, I'd apreciate it.

 

[gemma-the-husky]

if you have a number of seconds then MOD will give you the remainder

minutes = sectotal /60

seconds = sectotal mod 60

 

[plog]

Explanations don't help that much.

Can you post some sample data and then what you want as a result from this sample data?

 

[Steve C]

Thank you Gemma-the-husky. Mod looks like the right answer - Will report back when I know more.

Sorry I've not put up sample data Plog - it's a reasonable request - and you asked me twice! But my Objects don't have simple names like Table1, Query1. I was afraid of hiding my question in a sea of irrelevant stuff.

 

[Steve C]

Dave – you have very made my day THANK YOU

For the benefit of anyone following – here’s what I did to get a sum of seconds in a query to appear as an amount of time – like 2 hours 20 minutes.

Table1 has a PKField and Field1. Field1 contains a number representing seconds. There is only 1 record in Table1.

Table2 has all my (proper) records. A FK to Table1 gives each of my proper records the number of seconds in Field1.

Query1 selects from Table2 and counts the ID Field. But I don’t just want a count of selected Table2 records, instead, I want the ID Count multiplied by the Table1.Field1 number of seconds. And, I want the result expressed not as a sum of seconds but in ordinary parlance – say; 2 hours 20 minutes.

My Query, set out below has Table1 & Query1 in the QBE top window.

The important bits were,

The “int” in Field:Hours which rounds down the hours to a whole number

The “Mod” operator in Field:MinutesRemainder which gives the remaining minutes after all the whole hours have been taken out.

Field: Count: ID
Table: Query1
Total: Count

Field: Hours: Int([Count]*[Field1]/3600)
Table:
Total: Expression

Field: Minutes: ([Count]*[Feild1]/60)
Table:
Total: Expression

Field: MinutesRemainder: ([Minutes] Mod 60)
Table:
Total: Expression

Field: Other
Table: Query1
Total: GroupBy
Sort:
Criteria: As required

I think I made a mistake with the Field:Minutes – perhaps I could have managed without it if I had thought about the divisions a bit more. But I doubt anyone will ever read this far down this post!!!

Thanks again Happy days!!!!

 

[94Sport5sp]

Hi:

Just a point for clarification. The Fix function might be a better choice than the Int function. The Int Function rounds and the Fix function discards the fraction.

So, in your case, if Field1 represents the total count of seconds then
fieldhours = fix(field1/3600)
and
fieldminutes = fix((field1 mod 3600) / 60)