Calculate elapsed time problem (1 Viewer)

Abi

New member
Local time
Today, 13:40
Joined
Jul 1, 2007
Messages
4
Hi,
I've been trying to calculate elapsedtime between two fields in a database.

I have field1 and field2 in short time format and the easy way "field2-field1" gives me the correct elapsed time in the short time format in a new field in my form.

But for some records I don't have the data to fill in field1 and field2, so I created field3 in my table where I can enter the time myself in the short time format.

Now I want a new column in my form with a function that gives me "field2-field1" OR field3 if field1 & field2 are empty.

I don't manage to do this. Can anyone give me a sollution how to program my function (options, declaration, function, ...).

anyone?

thx
 

Brianwarnock

Retired
Local time
Today, 12:40
Joined
Jun 2, 2003
Messages
12,701
Try
If isnull(field1) then
resultfield = field3
else resultfield =field2-field1
end if

Brian
 

Abi

New member
Local time
Today, 13:40
Joined
Jul 1, 2007
Messages
4
Thx, I've tried this but then I get 0:00 as elapsed time for every record.

Public Function totaltime()
Dim interval As Date
If IsNull(ArrivalTime) = True Then
interval = FlightTime
Else
interval = ArrivalTime - DepartureTime
End If
totaltime = interval
End Function

So, when I put "=totaltime()" into the control source of the column in my form I get 0:00.

When I put "=FlightTime" in the control source I get the correct flighttime for the records with a flight time and no departure and arrival time, and no value for the other records.

When I put "=ArrivalTime-DepartureTime" in the control source then I get the correct elapsed time for the records with the times and no value for the records without timings (but with flighttime).

So I'd like a function wich gives me a value in both cases.

The aim is to put another box in the footer with the sum of the totaltimes. When I put "=Sum([TotalTime]) in that control source, i get "#Error".

How do I fix this second problem?

Thx
 

boblarson

Smeghead
Local time
Today, 05:40
Joined
Jan 12, 2001
Messages
32,059
A suggestion:

1. Use DateDiff instead of subtracting directly

2. Use the NZ function to handle possible nulls
 

Abi

New member
Local time
Today, 13:40
Joined
Jul 1, 2007
Messages
4
Ok it works when I put this directly into the control source:

=NZ( [Flight Time] )+NZ( [Arrival Time] - [Departure Time] )

But I don't manage to do this via the module.

Could someone give me an example of a module to do this? I get external name not defined when compiling this:

"Option Compare Database

Public Function totaltime()

totaltime = Nz([Flight Time]) + Nz([Arrival Time] - [Departure Time])

End Function"


Someone can help me with this and tell me how to make a sum of the values of this column in a footer of the database?
 

boblarson

Smeghead
Local time
Today, 05:40
Joined
Jan 12, 2001
Messages
32,059
You need to do two things if you are going to use the function:

1. pass the variables to it

2. and then return a value

[Flight Time] and [Arrival Time] and [Departure Time] means nothing to it as it doesn't have those fields available to it.

So, rewrite your function to this:

Code:
Public Function totaltime(dteFlightTime As Date, dteArrivalTime As Date, dteDepartureTime As Date) As Date

totaltime = dteFlightTime + dteArrivalTime - dteDepartureTime

End Function

And to call it you would use

=totaltime(Nz([Flight Time],0), Nz([Arrival Time],0), Nz([Departure Time],0))
 

Abi

New member
Local time
Today, 13:40
Joined
Jul 1, 2007
Messages
4
OK thx a lot!

I'm new to VBA in access, I had some experience with VBA long ago but ... it's all gone.

Now I'm having the next problem in creating my database.

As the values of the field "Total Time" in the form are calculated, it seems not possible to make a sum of it in the footer of the form.

Any ideas?

Thx!
 

Users who are viewing this thread

Top Bottom