Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Posts
    182

    Unanswered: Calculated field conversion

    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. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    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
    currently using SS 2008R2

  5. #5
    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. #6
    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. #7
    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!!
    and in your query:
    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.
    currently using SS 2008R2

  8. #8
    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
    currently using SS 2008R2

  9. #9
    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. #10
    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. #11
    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. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote 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. #13
    Join Date
    Apr 2004
    Posts
    182
    Thanks anyway Mike. I've been trying it but I keep getting errors.

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote 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. #15
    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.
    currently using SS 2008R2

Posting Permissions

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