Subtracting two text fields

[tamer64]

How do I Subtract one text field from another in a query. I have an employee cap field where its default value is always 50:00. I need to be able to subtract a text field "SumUTime" from the "Employee_Cap" field.

e.g.

50:00 [Employee_Cap]
37:15 [SumUTime]

12:45 [TimeAvailable] Desired output

 

[SkipVought]

hi,

TimeAvailable: TimeValue([Employee_Cap])-TimeValue([SumUTime])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tamer64]

Hi Skip,

I tried using this [blue]TimeAvailable: TimeValue([Employee_Cap])-TimeValue([SumUTime])[/blue] in an expression but I receive the following "#Error"

 

[MajP]

I believe timeValue needs a string parameter that represents a time. Something less than 24 hours. 50:00 will fail. I assume your times are hours and minutes not minutes and seconds. Modify the code for minutes and seconds.

I would drop these two functions in a standard module:

Public Function ElapsedTimeFromStrings(ByVal startTime As Variant, ByVal EndTime As Variant) As Variant
 If Not IsNull(startTime) And Not IsNull(EndTime) Then
   startTime = CDate(Split(startTime, ":")(0) / 24 + Split(startTime, ":")(1) / (24 * 60))
   EndTime = CDate(Split(EndTime, ":")(0) / 24 + Split(EndTime, ":")(1) / (24 * 60))
   ElapsedTimeFromStrings = ElapsedTime(EndTime, startTime)
 End If
End Function

Function ElapsedTime(EndTime As Variant, startTime As Variant) As String
    Dim Interval As Date
    Dim strOutPut As String
    
    ' Calculate the time interval.
    Interval = EndTime - startTime
     
    ' Format and print the time interval in hours and minutes.
    strOutPut = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn")
    
    ' Format and print the time interval in seconds.
    'strOutput = Int(CSng(Interval * 24 * 3600)) & " Seconds"
        
    ' Format and print the time interval in minutes and seconds.
    'strOutput = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
        & " Minutes:Seconds"
    
    ' Format and print the time interval in hours, minutes and seconds.
    'strOutPut = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
           & " Hours:Minutes:Seconds"
        
    ' Format and print the time interval in days, hours, minutes and seconds.
     'strOutPut = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
        & " Hours " & Format(Interval, "nn") & " Minutes " & _
        Format(Interval, "ss") & " Seconds"
    
     ElapsedTime = strOutPut
End Function

To use in a query.

Select ElapsedTimeFromStrings([field1],[field2]) as ElapsedTime ...

 

[tamer64]

MajP, I will give this a try. Thank You.

 

[tamer64]

MajP,

I have applied both modules to my DB. When the input value for the field "SumUTime" is equal to or greater than 1:00 minute, the "TimeAvailable" field is correct. The output value displays a negative value which I can easily remove by using the Trim function.
[blue]
50:00 [Employee_Cap]
37:15 [SumUTime]
-12:45 [TimeAvailable] Desired output
[/blue]
Here is the problem I now have. When the input value is less than 1:00 minute for the field "SumUTime", the output value for "TimeAvailable" is incorrect. When the input value is entered as "00:15" for the the field "SumUTime", the value is being added to the "Employee_Cap" field instead of subtracting from the "Employee_Cap" Field.

How can I correct the issue when the field "SumUTime" input values are less than 1:00 minute whereby correctly displaying the correct values for "TimeAvailable".
[blue]
50:00 [Employee_Cap]
00:15 [SumUTime]
-50:15 [TimeAvailable] "Output should be "49:45"
[/blue]

Appreciate any and all assistance anyone could provide

 

[MichaelRed]

the example values are easily interperted as either hh:mm OR mm:ss. This can and will cause confusion and lead to errors, as evidenced in hgte code above. Users can enter / generate values using a mixture of hte formats, hence, at some times you will be 'subtracting' hh:mm from mm:ss or vice versa.

A better soloution would be to ALWAYS store the values as TIME and require all three portions be entered (e.g. hh:mm:ss) then you could also limit the used to be less than the avalibble.

The database design appears (from way over here) to be less than optimum!

Michael Red


MichaelRed

 

[TheAceMan1]

How are ya tamer64 . . .

Biggest problem is you can't use any of the time functions due to 24Hr crossover (already pointed out by [blue]MajP[/blue]). This dictates a [blue]custom function[/blue], and that function has to compute in sexagesimal or [blue]Base 60[/blue]. So in a module in the modules window, copy/paste the following function:

[blue]Public Function TimeDiff(HiTime As String, LoTime As String) As String
   Dim aryEC, arySUT, minEC As Integer, minSUT As Integer
   
   aryEC = Split(HiTime, ":")  [green]'Setup Array[/green]
   arySUT = Split(LoTime, ":") [green]'Setup Array[/green]

   minEC = aryEC(0) * 60 + aryEC(1)    [green]'convert to minutes[/green]
   minSUT = arySUT(0) * 60 + arySUT(1) [green]'convert to minutes[/green]


   TimeDiff = Int((minEC - minSUT) / 60) & ":" & Format((minEC - minSUT) Mod 60, "00")

End Function[/blue]

In your query, setup a custom field as follows:

[blue]TDiff:TimeDiff([Employee_Cap], [SumUTime])[/blue]

Thats it! ... perform your testing.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MichaelRed]

... the FORMAT conversion between the user perspective (string) and data perspective (TIME) could obviate the need fo the custom math function, and - by virtue of forcing the consistiency of the values - provide consistiently correct results?


Michael Red


MichaelRed

 

[MajP]

I have no idea what you are talking about. My provided code provides the correct answers. I can not replicate your problems. The elapsed times are correct.

 

[tamer64]

Thank you all for your replies.

MajP, The modules worked correctly. In my query I made the mistake of placing Employee_Cap field as my end time. When I swapped both the Employee_Cap field and SumUTime field the output was correct.

Thank you.