problematic date/time manipulation 2

[Guest_imported]

i have a query with 2 date/time fields. i want an extra column that stores the difference between the two fields, in minutes. again, the 2 above-mentioned fields contain the time (eg. 5:00 AM)
I tried MINUTE(["2ndfield"]-["1stfield"]) but it doesn't work(shows 0 as result). i tried converting the 2 date/time fields to text fields, but it still does not work. any suggestions?

 

[JerryDennison]

Use the DateDiff() Function.

DateDiff("n", [2ndfield], [2stfield])

 

[Guest_imported]

Hi, guys!

Could you help me. I need to convert minutes to the next format:
hh:mm:ss
say I have 2225.6666 min and I need it in hh:mm:ss


Thanks

 

[JerryDennison]

You'll have to write your own function to do this.

Int([Minutes]/60)&":"&Int(60*(([Minutes]/60)-Int([Minutes]/60)))&":"&60*((60*(([Minutes]/60)-Int([Minutes]/60)))-Int(60*(([Minutes]/60)-Int([Minutes]/60))))

 

[MichaelRed]

Jerry's function works however, for the faint of heart, the function below accomplishes the requested functionallity in a slightly different manner, with some comments to outline the inner workings.

Public Function basMinsToHrMnSec(Mins As Double) As String

    Dim Days As Integer         'Number of Whole Days
    Dim FractDays As Single     'Fractional Days
    Dim tmpOut As Date          'Temp Storage for Partial Soloution
    Dim tmpDayHrs As Integer    'Temp for Hours
    Dim tmpMins As Integer      'Minutes of Fractional Day
    Dim tmpSecs As Integer      'Seconds of Fractional Day

    Days = Mins \ 1440                      '1440 Minutes = 24 Hrs * 60 Mins
    FractDays = (Mins / 1440) - Days        'Fractional Part of Whole Day
    tmpOut = Format(FractDays, "hh:mm:ss")  'Format the fractional part as Hr:Min:Sec

    tmpDayHrs = (Days * 24) + Format(tmpOut, "hh")      'Actual Hours
    tmpMins = Format(tmpOut, "n")                       'Actual Minutes
    tmpSecs = Format(tmpOut, "s")                       'Actual Seconds
    
    basMinsToHrMnSec = tmpDayHrs & ":" & tmpMins & ":" & tmpSecs    'Format Return Value

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JerryDennison]

Faint of heart? ;-)

 

[MichaelRed]

Jerry,

That's a LOT of very detailed arithmatic and concatenation - all of which needs to be EXACTLY correct or the user just gets an error. I was just attempting to supply a bit of 'education'/explination of what was going on. The only difference is I used some more formatting and a little less math. But thanks for noticing.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JerryDennison]

You did good Michael. I was just having a little fun at my own expense. It wasn't meant to be at your expense, but mine. And you are correct, something the least little bit out of place with what I posted and you could have a very difficult time tracking it down.