Hy, Need Help with formula

[Arsys]

Hello , I`m new to forum and to access programing.
I have 2 fields, like this:

Field 1 Field 2 Field 3
141:30 146:40 5:10 <- result from Field 2 - Field 1

The numbers r time cumulated on a device. Problem is I dont know how to make access let me enter a bigger hour number in field 1 and 2.... it onli lets me to insert at max 24.

Thanx

 

[sneuberg]

Well there's only 24 hours in a day. If you want a larger time difference between two Date/Times you will have to include the date. If you use the General Date format you can entering the date and time by enter the date a space and then a time. See attached screen shot.

 

[sneuberg]

On the other hand if your data is really just in hours may you should store them as singles or double and convert them to an hours:minute format for display. At the bottom of https://bytes.com/topic/access/answers/810270-formatting-number-hours-minutes I found

Format(-(-Int(TheHours)), "#0") & Format(TheHours / 24, "\:nn")

which seem to do pretty well at convert a number to an hours:minutes format. If you want some to go the other way you could use the split function and then just do the math. Of course you would need some error check you make sure the data was entered correctly.

 

[boerbende]

Excel can handle that correctly.
If you want to use Access, you can try this:
Create text fields field1 and field2 with data input mask ###:##
Now the calculation of fields can be
Left(field1,3) - Left(field2,3) + (right(field1,2) - right(field1,2)) / 60
Now you get 5,1667 but this you can split back to hours and minutes again

 

[arnelgp]

one solution is to make the field as short text. on the form create an input mask of "##0:00". when saving include the colon.
calculation can be done through the use of user-define function:

Result: fnSubtractStringTime([Field1], [Field2])

Public Function fnSubtractStringTime(ByVal p1 As Variant, p2 As Variant) As String
    ' p1 in the format 999:99
    ' p2 in the format 999:99
    Dim t As String
    Dim h1 As Integer
    Dim h2 As Integer
    Dim m1 As Integer
    Dim m2 As Integer
    
    If IsNull(p1) Then p1 = "000:00"
    If IsNull(p2) Then p2 = "000:00"
    p1 = p1 & ""
    p2 = p2 & ""
    If p1 > p2 Then
        t = p2
        p1 = p2
        p2 = t
    End If
    h1 = Val(Split(p1, ":")(0)):    m1 = Val(Split(p1, ":")(1))
    h2 = Val(Split(p2, ":")(0)):    m2 = Val(Split(p2, ":")(1))
    If m2 < m1 Then m2 = m2 + 60: h2 = h2 - 1
    m1 = m2 - m1
    h1 = h2 - h1
    h1 = h1 + (m1 \ 60)
    m1 = m1 Mod 60
    fnSubtractStringTime = h1 & ":" & m1
End Function

 

[The_Doc_Man]

Arnel beat me to the SPLIT suggestion, but that is the way I would do it.

 

[sneuberg]

Access Date/Times are actually stored as doubles which represent days. 0 represents December 30, 1899. Find more interesting facts at https://support.microsoft.com/en-us/kb/130514

The reason I bring this up is that like Microsoft I believe the best way to deal with time and dates is to treat them as numbers and format them into dates or times when they are presented to the users. I think you will be better off in the long run if you adapt that strategy now with you problem with hours than adopt your own storage format use a string data type.

 

[Arsys]

Thanx alot for your answears... i guess il stick to excel. Or maibe install VFP to see if it knows to calculate like excel.