formating hours and minutes to 0.00 format

[live2ride]

i have 2 fields "Hours" Integer and "Minutes" integer and i need to format them into 0.00 Integer field
reason for this is because our time table doesn't store the actual time 24hrs and 60 min, instead it uses numeric number 0 to 100 and 101 being 1hour and 1min but it needs to be converted to 25int per 15 min so 1hour and 25 min needs to be 1.50


i have no control over the time table so i cant make any adjustments to tables

i am able to make function to do the conversion of numbers but all i can do is make a string to look i need it to.
does anyone know how i can convert it to Integer?

[June7]

You have two fields Hours and Minutes? Each field stores a value between 0 and 100? How do you get 1 hour and 1 minute to be 101? That looks like 100 hours + 1 minute (by addition) or 10 hours & 1 minute (by concatenation).

You can concatenate Hours and Minutes to get time: Hours & ":" & Minutes

You can also concatente to get decimal hours but need to do calc on the minutes part to convert to decimal, with rounding to 15 minutes. Try:
Hours & "." & Format(Minutes/15,"0")*15/60

[live2ride]

im using below code to convert hours and minutes to format i need it in, but i need output to be Integer instead of Double or String
Public Function bHours(Hrs As Integer, Min As Integer) As Double
Dim Hours As Integer
Dim Minutes As Integer
Hours = Hrs
If Min <= 15 Then
Minutes = 25
End If
If Min > 15 And Min <= 30 Then
Minutes = 50
End If
If Min > 30 And Min <= 45 Then
Minutes = 75
End If
If Min > 45 Then
Minutes = 0
Hours = Hrs + 1
End If

bHours = Hours & "." & Minutes
End Function

[June7]

I was editing my post when you posted. Your op said you need result like 1.5 hours but now you say you need an integer? You don't want decimal part then you need to use only minutes unit. Convert the hours part to minutes and add.

With your code 31 minutes will round up to .75 hour instead of down to .5 hour. Is that what you want?

[live2ride]

i have startDate datetime, endDate datetime, minutes integer, hours integer and total minutes as integer, how could i accomplish what i need the best way? not sure how to do what youre suggesting, im very new to access and vba

[June7]

I don't really understand what you want. I thought I offered you something but your descriptions of requirement are conflicting. Please be clear on what the final output should appear as.

The expression I offered could be in a query to construct a field or in the ControlSource of a textbox. However, you already have a function to return a value. This is not what you want?

[live2ride]

Private Sub Command26_Click()
Dim Hours As Integer
Dim Minutes As Integer
Hours = Me.Hours
Minutes = Me.Minutes
me.txtBaHours = bHours(Hours,Minutes)
End Sub
i cant get the result to be added to me.txtBaHours in format that is being outputed from Function above
so if Hours = 2 and Minutes = 20
msgBox bHours(Hours,Minutes) = 2.5 (result is good)
me.txtBaHours = bHours(Hours,Minutes) - (result is "2")

[live2ride]

looks like i didnt get part of your message when you edited. i tried you code "Hours & "." & Format(Minutes/15,"0")*15/60" but i get the Run-Time error, i thinks its because of the string (".") in the code and my field is Number

[June7]

Concatenation does not care if the field is number or text. However, I did not fully test the expression and concatenation is not correct operation. Just add.
Hours + Format(Minutes/15,"0")*15/60

This expression will return 2 hours 20 minutes as 2.25, not 2.50. If this is not what you want then use:
Hours + Switch(Minutes=0,0,Minutes>45,1,Minutes<=15,0.25,M inutes<=30,0.50,Minutes<=45,0.75)

Where did you try the expression?

[live2ride]

Dim B as String or Double
B = Hours + Format(Minutes / 15, "0") * 15 / 60
MsgBox B - result "2.5"
me.baHours = B - result "2.00"

Dim B as Integer
B = Hours + Format(Minutes / 15, "0") * 15 / 60
MsgBox B - result "2"
me.baHours = B - result "2.00"

[June7]

Again, did some more editing on last post while you posted. You are on top of this, really hurting for solution!LOL

I don't know what you are trying to tell me in that last post. Use the function that gives you want you want.

[live2ride]

lol, what i was trying to say in the last post is that MsgBox displays the correct results but when i try to update my text box, those results are not being passed by for example
Dim B as String
B = Hours + Format(Minutes / 15, "0") * 15 / 60
MsgBox B ( displays the correct results "2.5")
but when i do
me.baHours = B ( i get wrong results in my text box - results in text box are "2.00" or just 2)

[June7]

Is baHours a field of form RecordSource? If it is a field is it Integer number type?

What do you want to happen to this calculated result - just display on form or report, are you trying to save to field? Saving calculated data dependent on other data is usually bad idea. It can be calculated when needed.

[live2ride]

baHours is Number field in my Table,which i would like to store this calculated result in
i have to update another table elsewhere with this calculated field which is using this format and i would like to keep the results in my table as well for easier comparison. im very new to access so im probably very confusing but this seems to me like the easiest solution so far.

[June7]

You said you have fields Hours and Minutes. These fields are used to calculate one decimal hours value. If you want to save this calculated value (such as 2.5) in another field then the field must be a Single or Double type (I always use Double). Saving the value will reqire either an sql UPDATE or INSERT action or VBA code behind form to set field value. You appear to have the latter.

If you want to provide db for analysis, follow instructions at bottom of my post.