1. Registered User
Join Date
Apr 2004
Posts
182

I have a calculated field that gives me 1.333333 hours how do I convert this into 1.20mins ? Here is the code I have tried :
SELECT EDATE, EMPLOYEE, OPERATION, DOWNREASON, JOB_ID, SUM(SETTINGH * 60 + SETTINGM) / 60 AS Setup, SUM(RUNNINHH * 60 + RUNNINGM)/ 60 AS Run, SUM(DOWNHOURS * 60 + DOWNMINS)/ 60 AS Down, DOWNTIME
FROM tblctjor
GROUP BY EDATE, EMPLOYEE, OPERATION, DOWNREASON, JOB_ID, SETTINGH, SETTINGM, DOWNTIME

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
that doesn't make sense...

What's the mathematical relationship you are trying to create?

how would 1.3333 hours be converted to 1.2 minutes?

3. Registered User
Join Date
Apr 2004
Posts
182
The first part gives me (settingH*60)60 mins plus (settingM)20mins = 80 mins divide that by 60 gives 1.33 when what I want is 1hr 20 mins. It's the divide by 60 that is wrong I was doing that to try to convert it to hours and mins. I alsow tried MOD but cannot get the syntax right.

4. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
methinks you have a table problem dressed up as something else.

why do you want to store hours and minutes in separate fields when everything since dbase through Word through Excel, certainly Access, and possibly even Notepad too (this last one is an exaggeration!) knows about datetimes, knows how to store them efficiently and unambiguously, knows how to do datetime maths and, most importantly for your current connundrum, knows how to display these strange creatures in normal human-expected formats.

yous should probably fix your tables, but you could even take your 1.33333 and exploit datetime handling:

times in A are fractions of a 24 hour day: 1.33333/24 is a valid access time which could be displayed with format\$() eactly as you wish. i forget if the format string is "hh:mm" or "hh:nn" but A's help will tell you.

izy

5. Registered User
Join Date
Oct 2003
Location
US
Posts
343
It is actually "hh:nn". so your query should look something like

format((SUM(RUNNINHH * 60 + RUNNINGM)/ 60)/24,"hh:nn")

6. Registered User
Join Date
Apr 2004
Posts
182
Inherited system with a huge amount of data to transfer is the reason that I am not changing how the data is stored.
When I try this in the query it says format is not a recognised function name but it works if I put hh:nn as the format of the textbox displaying the data.
Thanks
Last edited by livvie; 06-19-04 at 04:16.

7. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
some guesswork....

in a global module:
Code:
```public function HandMtoDT(inHours as integer, inMins as integer) as date
HandMtoDT = ((inHours + (inMins/60))/24) 'parentheses are free
end function```
UNTESTED!!
SELECT ........... SUM(HandMtoDT(DOWNHOURS, DOWNMINS)) AS Down
etc
but i have my doubts about this bit ...maybe it's worth a try. if it doesn't work out you might need to to a two-step
/UNTESTED!!

then when you want to display in human-expected format
format\$(Down, "hh:nn")

izy
Last edited by izyrider; 06-19-04 at 04:34.

8. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
sorry - that was unnecessarily complicated! why not:
SELECT ... SUM((DOWNHOURS + DOWNMINS/60)/24) AS Down ...

and if that works, why not:
SELECT ... format\$(SUM((DOWNHOURS + DOWNMINS/60)/24), "hh:nn") AS strDown ...

izy

9. Registered User
Join Date
Apr 2004
Posts
182
OK it works with the /60/24 and then format the text box except when 24 hours is entered this displays as 00:00 ??

10. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Here is something you can use and use for some other things as well. This is assuming that your data is like 28.43 and meaning 23 hours and .43 of an hour.

This is something I use to remove spaces and characters like ( [_- etc from phone numbers and I have just pulled part of out and made a couple of changes.

There are 3 query fields created

Exp2abcd: IIf([SearchNum] Is Not Null,InStr([CL Ph Home],[SearchNum]),0)

The field [SearchNum] is where a character is entered. In this a case full stop is entered. The field [CL Ph Home] would be where 28.43 is entered.

The result of this field will be a 3. The 3 means that the decimal point is the third character. If it was 678.23 hours then the result of this field would be 4

TestExp2abcd: ((IIf([Exp2abcd]>0,Mid([CL Ph Home],1,([Exp2abcd]-1)),0)) & " " & "Hours") & " " & [TestExpabcd]

The result of this field will be 28 Hours 26 Minutes. Note that field [Exp2abcd] is the field above where the result was 3. The field [TestExpabcd] is the field below

TestExpabcd: (CLng(IIf([Exp2abcd]>0,Mid([CL Ph Home],([Exp2abcd]+1),100),0)*0.6)) & " " & "Minutes"

The result of this field is 26 Minutes

The CLng is rounding off the the result of .6 X 43 which is 25.8 to 26

If instead of 28.43 you had 28(43 and entered a ( in the field [SearchNum] then you would still get a final result of 28 Hours 26 Minutes

Elsewhere in this query where I extracted this stuff from I have some that already set up characters such as [)[)- but if some phone numbers have been done as 123 (234) 12*3 then the preset stuff will reduce everything to 12323412*3 and when I enter an * in Search number then it the number will be come 123234123. This achieved by a macro feeding the result by a SetValue back into the phone number field and repeating itself and then moving to the next record and doing it again.

Have fun

Mike
Last edited by Mike375; 06-19-04 at 05:25.

11. Registered User
Join Date
Apr 2004
Posts
182
How do I incorporate that into a SQL statement that is setting the record source of a form.

12. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by livvie
How do I incorporate that into a SQL statement that is setting the record source of a form.
Don't know how you do that as I have it as 3 separate calculated fields in a query

13. Registered User
Join Date
Apr 2004
Posts
182
Thanks anyway Mike. I've been trying it but I keep getting errors.

14. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by livvie
Thanks anyway Mike. I've been trying it but I keep getting errors.
Keep in mind that each field is feeding of the results of the other field.

Perhaps you could transfer the results from the calulated field with the final result to a field in your table and then use that field in your table instead of the field that has the 23.67 type entries

Mike

15. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
24 hours = 1 day, 0 hours, 0 minutes, 0 seconds, so "hh:nn" is 00:00

note that the datemath still works fine
i.e. SUM(many times 24 hours) comes out as many days, so your query should be fine.

but now you need to display it.
you probably consider "dd hh:mm" as cheating,

so now lets try to get to "hhhhhh:nn" (an invalid formatting string as far as i know)

the bit before the decimal on a datetime is "days", so dropping the stuff after the decimal gets you the number of 24 hour days...

try displaying your datetime query return as:
cstr(int(datetime)*24) & ":" & format\$("nn", datetime)

LATER: NO, THAT WONT WORK!

izy
Last edited by izyrider; 06-19-04 at 06:53.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•