Adding Hours

I've been trying to subtract times i.e. 15.40 to 16.10 and cummulatively add these hours in order to track flight hours.

I can complete this if I convert the times to decimal times i.e. 1530hrs become 15.50.

I would like to be able to get Access to subtract and track hours with out this decimalisation. Is this possible?

Help Please....

I don't know if it is possible with a value such as 15.45 which would reference as 15.75 as decimal. Access stores dates as whole numbers and time as a decimal of that number.

I have only every just used the time the way access stored it and done calulations to it in that state and then converted it to something that makes sense to the users.

Good Luck
S-

Hi

1. To Add or Subst. time values, they should be first converted to minutes. The following function does that:

'*********************************
Private Function TimeToMinutes(strTime As String, Optional Separator As String = ":") As Long
TimeToMinutes = Val(Left(strTime, InStr(1, strTime, Separator) - 1)) * 60 + Val(Mid(strTime, InStr(1, strTime, Separator) + 1))
End Function
'*********************************
Note: if you don't use ":" as separator character between hour and minute digits but you use a different one, then you must include the separator character in the function call, as the second parameter.

2. After converting the time values to minutes, they can be normally added or substracted. After calculating the minutes, the return value should be converted back to hh:mm format. The following code does that:
'*********************************
Private Function MinutesToTime(lngMinutes As Long, Optional Separator As String = ":") As String
Dim lngHourPart As Long
Dim lngMinutePart As Long
Dim strHourPart As String
Dim strMinutePart As String
lngMinutePart = lngMinutes Mod 60
lngHourPart = (lngMinutes - lngMinutePart) / 60
If lngHourPart < 10 Then
strHourPart = "0" & Trim(Str(lngHourPart))
Else
strHourPart = Trim(Str(lngHourPart))
End If
If lngMinutePart < 10 Then
strMinutePart = "0" & Trim(Str(lngMinutePart))
Else
strMinutePart = Trim(Str(lngMinutePart))
End If
MinutesToTime = strHourPart & Separator & strMinutePart
End Function
'*********************************
Note: if you do not specify a separator character in the function call then a ":" will be the separator character.

3. And here we come. The function that makes the calculation:
Public Function CalculateTime(strTime1 As String, strTime2 As String, Optional Operation As String = "Add") As String
Dim lngTurnValue As Long
Dim SubValue As Long
If Not (Operation = "Add" Or Operation = "Substract") Then
Operation = "Add"
End If
If Operation = "Add" Then
SubValue = TimeToMinutes(strTime1) + TimeToMinutes(strTime2)
Else
SubValue = TimeToMinutes(strTime1) - TimeToMinutes(strTime2)
End If
If SubValue > 1440 Then
SubValue = SubValue - 1440
Else
If SubValue < 0 Then
SubValue = SubValue + 1440
End If
End If
CalculateTime = MinutesToTime(SubValue)
End Function
This is the function that you call from your query or form.
Note: this function IGNORES the date! It does NOT add 1 to the date if the result of the operation is beyond 1440 minutes (which is 24 hours), and does NOT substract 1 from the date if the result of the operation runs under 0.

Bonus:
Here is a sub to test the function group (this should return "23:30"):
'*********************************
Private Sub test()
MsgBox CalculateTime("15:30", "16:00", "Substract")
End Sub
'*********************************
I hope this helps.

Regs,

TBÁrpi

Sorry, line 12 of CalculateTime function is:

If SubValue > 1440 Then

instead of:

If SubValue > 480 Then

BRegs,

TBÁrpi

TBÁrpi

Very nice. I will have to remember this.

S-

Ruth,

Of course you can send, if so, then use LostPilgrim@email.com.

BUT, to be honest, I give you trawl rather than fish... and don't worry about thinking you stupid. No one on Earth is born with programming knowledge.

The code I sent you should be pasted into a Module. Go to Modules tab, click New, and copy-paste the codes between the '********************* lines of my message. Then click Debug->Compile menu to

ensure that there is no compile error in the code. Then save the module. Give it any name.

To run the sub Test, place the cursor anywhere WITHIN the sub Test, and press F5 on the keyboard. A message box should appear showing 23:30. If so, then it's working OK. If you wish to see how it

works step-by-step, place the cursor anywhere WITHIN the sub Test, and press F8 on the keyboard. As you press F8 again and again, the execution goes a step, and highlights the line that is going to

be next executed. During step-by-step execution, if you move (and not click) the mouse above a variable, a tool tip will appear and show the current value of that variable.

Now, let's say you have a table called 'tblTime' with two fields:
Depatrure (type: text, length: 5)
FlightDuration (type: text, length: 5)

Create the table, and add some records to it.
Both field should be filled in HH:MM format, eg. 12:40, 05:15 etc.

Now, go to queries, click New.

Close the Show Table dialog box without adding any tables, and switch to SQL view. Paste the following SQL command there:

select Departure, FlightDuration, CalculateTime([Departure],[FlightDuration]) as Arrive from tblTime

Now switch to Design view, and you can see how the query is built visually. If you execute the query, it should show you the Arrive times.

Of course, if you already have a table to run the query on, then you can use it, but in this case the SQL command above probably won't work, so you need to edit/design your own query that works with your table.

I hope this helps. Anyway, if you still experiencing problems, send me the file.

BRegs,

TBÁrpi

Here's the sample file.

TBÁrpi

My God, you're a genius.

Would it be possible to total all the hours? I couldn't manage to do it the normal way, and I didn't want to screw with your coding!!
If the aircraft already has hours up on it, could that figure be added to the total?

Note how I have already managed to screw with your work!! (sorry could not upload)

Ruth,

[I am sharing this essay, because I think it can help other beginners.]

Now, as it's 01:25 in the morning, I'm going to just describe my idea.

You wrote earlier that you can use Excel for several calculations for this issue. Now, I think you shouldn't use both Excel and Acces because then you run two parallel application for a single task.

I think you first should choose between Excel and Access, but, of course, I do recommend using Access. I am not biassed against Excel. I know very well that tehre are many things that easier to carry out via Excel. But having looked at your problem and read your earlier emails I realized the mission of your application, and I think the solution can be more efficient in Access than in Excel. Access is able to run all the calculations for you. Excel has some database-like functions like HLOOKUP, FLOOKUP etc., but has no capability of relationships between tables, referential integrity, indexing and so on.

I don't really know the current completeness of your database but if I was charged with developing this application, I would start with something like the following:

- Create a table for the pilots. Data stored here would be Name, rank and "hours up" for the pilot, as a legacy data, etc. Table name would be tblPilot.
- Create a table for aicraft models. I would store here such data as type, model, manufacturer, etc. which related to a model and not to a single aircraft. I would call this table tblModel.
- Create a table with aicraft data. I would store here such data as model, serial number, date of manufacture, date of purchase, date of installation, nickname etc. I would store here those data which can be only ONE value for each aircraft, and rarely or never change. I would store here the "hours up" for each aircraft, as a legacy data. I would make sure that each aircraft has a unique identification number. The best is an AutoNumber field. I know serial number is identical, but seeking and sorting records are much faster if the ID is issued by Access when a new record is added. An AutoNumber means nothing for the user, so it can be hidden from them. Table name would be tblAircraft.
- Create a table with all the inspection/maintenance activities that should ever be carried out on a single aircraft. I would store here for example the description of the maintenance (eg. "oil charge", or "checking fuel pipe" or whatever, I don't really know what they can be for an aircraft). I also would create an AutoNumber field for record identification. Table name: tblMaintenance.
- Create a table for all the parts that should ever be changed in a single aircraft. I would store in this table such as Part Number, Description, Manufacturer, etc. I would store here the windscreen, for example. And again, an AutoNumber for record identification. Table name: tblPart
- Create a table to set up that how often a particular Model should be Maintenanced. This would have a field for defining the Model, a field for defining the Maintenance, a field for defining the time period the Maintenance should be done, and a field for the number of cycles the Maintenance should be done. This table would create a many-to-many relationship between tblModel and tblMaintenance tables. This table would exactly tell you which Model needs which Maintenance and when.
- Create a table to set up that how often a particular Part should be replaced in a Model. This would have a field for defining the Model, a field for defining the Part, a field for defining the time period the Part should be replaced, and a field for the number of cycles the Part should be replaced. This table would create a many-to-many relationship between tblModel and tblPart tables and would exactly tell you which Model needs which Part to be replaced and when.
- Create a table for logging those activities when an aircraft is with running engines for any reason. The reason can be a flight excercise, or anything that requires running engines. I would store here the Off Blocks, On Blocks, Departures, Arrives, pilot name, etc., all data that related to the activity. I would call this table tblLog.
- As a bonus, I would create a table for fuel fills.

With these tables set up, and maintaining the data in them, and accurately updating the log, you can retrieve any Maintenance or Part replacement needed for any single aircraft. And of course, you could retrieve more much statistic data such as "hours up" for a pilot, etc. There are many-many data mining possibilities e.g. consumption analysis, which can help you to keep fuel costs under control.

I know, building a database application is a bit more than just creating tables. There are many queries, forms and VB code required to get the application work, and deliver the solution.

Note: having a good database is having the power of control.

I hope this makes sense.

Good luck, and if you get stuck, let me know.

Árpi