=Sum(DateField)

[Jarryd]

Hi,

I have a subform (datasheet) that will be used to enter the length
(hh:mm:ss) of mobile calls. I then want the values in that field tallied up
and the total to be used as the value for a control on the main form. So I
have created a text box called txtCallDurTtl and set its control source to
=Sum(txtCallDur). I then add records in the subform using various times for
the values in the Duration field (01:02:03, 12:45:01, etc.) and expect the
txtCallDurTtl to add it all up but instead it displays "#ERROR". Why? I
have used this method before to add up currency and numbers and it works
fine. Is it something to with the fact that I am working with Date/Time/

TIA,

Jarryd

 

[Jarryd]

Hi everyone,

Nevermind this one. Silly me I am trying to add a text box and not a field.
Changed that and it now works better.

Sorry to have wasted your time if you read my original post about this.

Jarryd.

 

[Rick Brandt]

Hi everyone,

Nevermind this one. Silly me I am trying to add a text box and not a
field. Changed that and it now works better.

Sorry to have wasted your time if you read my original post about
this.

Beware that DateTime fields in Access are for storing points in time, not
amounts of time. When your sum is higher than 24 hours it will "wrap around"
and not display what you want.

Durations should be stored in numeric fields holding number of seconds or number
of minutes, etc..

 

[Jarryd]

Hi Rick,

So how exactly do you store a number as time? Do you have to convert it, or
is there a simpler way?

TIA,

Jarryd

 

[Duane Hookom]

Is "txtCallDur" a date/time field in your table?
Make sure the name of the control is not the name of a field.
I would not use a date/time field. A numeric field to store the number of
minutes or seconds would be better.

 

[Rick Brandt]

Hi Rick,

So how exactly do you store a number as time? Do you have to convert
it, or is there a simpler way?

You store it (as I said) as a number of units. For example you could store
the total number of seconds. Then for display purposes you can use an
expression to convert this to an hh:nn:ss format.

You basically use integer division (SomeValue\60) and the Mod operator to
get the remainder.

Example: 12345 seconds...

?12345\3600
3 (hours)

?12345 Mod 3600
1545 (seconds as a remainder)

?1545\60
25 (minutes)

?1545 mod 60
45 (seconds as a remainder)

so...

?12345\3600 & ":" & Format((12345 mod 3600)\60,"00") & ":" & Format((12345
mod 60) mod 60,"00")
3:25:45

 

[Jarryd]

Hi Duane and Rick,

Firstly, to answer Duane's question, txtCallDur is a texbox. Now, what I
don't get is why this isn't easy. I would have thought that the need to
store something as time would be commonplace and so datatype of time would
have been created. Nevertheless, I will continue along this path of "mod"s.
So if I get what you saying I could declare the value I want to use as an
integer, e.g Dim intTime as Integer, intTinme = Me.txtCallsDur. Use intTime
as the value to be converted/formatted as time, e.g.:

'" & intTime & "'\3600 & ":" Format(('" & intTime & "' mod 3600)\60, "00") &
":" & Format(('" & intTime & "' mod 60) mod 60,"00")

So now I can get data out and when I add all the time up (stored in seconds
as number) I can use that method to display the total in hh:mm:ss. But how
do I enter the value. It is going to make it that much harder for people to
read 01:25:53 on a page, then convert it to seconds so it can be entered in
to the textbox. So what I am imagining is that I would have to change the
field to unbound, and use something like afterupdate to convert it to a
number (total seconds) so that it can be stored in the table. And EVERY
time the number is displayed the same thing will have to happen. Am I right
in what I assume, because it seems like quite a lot of work to achieve
something very simple. I hope I am wrong.

Please let me know.

TIA,

Jarryd

 

[Jarryd]

Hi Chaps,

I see where I might have been stupid. It would be better to use the value
of a field in a recordset (clone the one used by the form and move it to the
right record) as the variable, not the textbox value.

But the rest of what I wrote is as I understand it. The reason I was using
date/time is because it is easy to add. I did notice that the time would
wrap round, but I hoped that the total number of hours racked up would be
less than 24. Not the most reliable assumption which is why I am keen to do
it the way you are recommending.

TIA for all you help. This would take me weeks without the help I get from
the users of this forum.

Jarryd

 

[Rick Brandt]

Hi Duane and Rick,

Firstly, to answer Duane's question, txtCallDur is a texbox. Now,
what I don't get is why this isn't easy. I would have thought that
the need to store something as time would be commonplace and so
datatype of time would have been created. Nevertheless, I will
continue along this path of "mod"s. So if I get what you saying I
could declare the value I want to use as an integer, e.g Dim intTime
as Integer, intTinme = Me.txtCallsDur. Use intTime as the value to
be converted/formatted as time, e.g.:

'" & intTime & "'\3600 & ":" Format(('" & intTime & "' mod 3600)\60,
"00") & ":" & Format(('" & intTime & "' mod 60) mod 60,"00")

So now I can get data out and when I add all the time up (stored in
seconds as number) I can use that method to display the total in
hh:mm:ss. But how do I enter the value. It is going to make it that
much harder for people to read 01:25:53 on a page, then convert it to
seconds so it can be entered in to the textbox. So what I am
imagining is that I would have to change the field to unbound, and
use something like afterupdate to convert it to a number (total
seconds) so that it can be stored in the table. And EVERY time the
number is displayed the same thing will have to happen. Am I right
in what I assume, because it seems like quite a lot of work to
achieve something very simple. I hope I am wrong.

That is pretty much what you want to do. In some case you can have the
user enter start and end times and then use DateDiff() to get the seconds
difference with one simple expression.

 

[OfficeDev18 via AccessMonster.com]

Hi Duane and Rick,

[quoted text clipped - 21 lines]

in what I assume, because it seems like quite a lot of work to
achieve something very simple. I hope I am wrong.

That is pretty much what you want to do. In some case you can have the
user enter start and end times and then use DateDiff() to get the seconds
difference with one simple expression.

How do you plan on tracking calls that originate at 11:59 pm and end at 12:01
am or later?

Suggestion: I've done this after much sweat and tears: store the begin and
end times as Doubles. At 4 pm today, that would be 38617.66666. If the call
lasts past midnight, the number left of the decimal (the 'days' portion) will
be 38618, and the number to the right (the hours, minutes, and seconds
portion) will be whatever. To get the actual duration, subtract the two
numbers and multiply by 24 [Duration = 24*(EndTime - BeginTime)]. If you want
to display it in hh:mm:ss format, it won't work if beyond 24 hours, as you
already know. You'll probably get away with 27.032 hours faster than getting
away with "hiding" 24 hours of phone usage. Besides, you could always sweat
through the code of building a string with the exact format, as long as you
don't use the format() function.

Example: say your addition of all durations comes out to 1.03439 (before
multiplication); cal it Tot. You can type

DayDur=Int(Tot): HrDur = Hour(Tot): MinDur = Minute(Tot): SecDur = Second(Tot)


txtTotDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur) + ":" + CStr
(SecDur)

This should display 24:49:31

 

[Jarryd]

That is some clever stuff. I haven't tried it yet. I have to get some shut
eye soon or I might as well just stay awake. But it all makes sense but if
you could please check bakc on this thread I would appreciate it.

TIA,

Jarryd

Hi Duane and Rick,

[quoted text clipped - 21 lines]

in what I assume, because it seems like quite a lot of work to
achieve something very simple. I hope I am wrong.

That is pretty much what you want to do. In some case you can have the
user enter start and end times and then use DateDiff() to get the seconds
difference with one simple expression.

How do you plan on tracking calls that originate at 11:59 pm and end at
12:01
am or later?

Suggestion: I've done this after much sweat and tears: store the begin and
end times as Doubles. At 4 pm today, that would be 38617.66666. If the
call
lasts past midnight, the number left of the decimal (the 'days' portion)
will
be 38618, and the number to the right (the hours, minutes, and seconds
portion) will be whatever. To get the actual duration, subtract the two
numbers and multiply by 24 [Duration = 24*(EndTime - BeginTime)]. If you
want
to display it in hh:mm:ss format, it won't work if beyond 24 hours, as you
already know. You'll probably get away with 27.032 hours faster than
getting
away with "hiding" 24 hours of phone usage. Besides, you could always
sweat
through the code of building a string with the exact format, as long as
you
don't use the format() function.

Example: say your addition of all durations comes out to 1.03439 (before
multiplication); cal it Tot. You can type

DayDur=Int(Tot): HrDur = Hour(Tot): MinDur = Minute(Tot): SecDur =
Second(Tot)


txtTotDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur) + ":" + CStr
(SecDur)

This should display 24:49:31

--
Sam


Message posted via AccessMonster.com

 

[Jarryd]

Hi Sam, Duane and Rick,

Well I have managed to get the data out and formait it correctly. What the
code is not doing is adding the correct data. I have set a where clause
that should filter the values to be added by the bill number. When I run
the query on its own it returns the correct total. But in the event it adds
up every value in the Durations field. I don't understand it. If it goes
in to debug mode I can rest the cursor over the variables and they all come
out correct, except for Tot = rst!Sum. Bill = the correct bill number which
is passed in to the query, so why is it not filtering correctly? Please see
code below:

-----------------------------------------------------------------------------
Dim db As DAO.Database
Dim rst As DAO.Recordset

'Total of filtered Durations
Dim Tot As Double

'The number of the current bill stored in a hidden textbox, txtBillHdn
Dim Bill As String
Bill = Form_frmMobile_Bills.txtBillHdn

Set db = CurrentDb

'Recordset filtered by Bill
Set rst = db.OpenRecordset("SELECT Sum([UK>Int_Calls].Durations) AS [Sum]
FROM [UK>Int_Calls] WHERE ((([UK>Int_Calls].Bill)= '" & Bill & "'));")

'Total equals sum of all values in Durations field, not filtered. This is a
problem!
Tot = rst!Sum
DayDur = Int(Tot): HrDur = Hour(Tot): MinDur = Minute(Tot): SecDur =
Second(Tot)
Me.txtIntCAllUKDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur) + ":"
+ CStr(SecDur)

-----------------------------------------------------------------------------

The other thing that I am thinking about is how do I make it possible to do
the reverse. I suppose it can't be that hard; I suppose use Cast or
something like that to concatenate the parts of the string that will be
used:

e.g. 3600 times the hours value, 60 * the minutes value, 1 * the seconds
value, and then add all three.

Am I on the right track? When do you think a datatype will be created that
stores time as an amount of time so that you don't have to do all this?
Honestly, it's an absolute nonsense.

The third thing I am having trouble with is getting the correct value out of
a recordset. I have want to add a value to a field (CallCost), and on
AfterUpdate it must tally up all the values in it field (filtered by Bill,
again). But the value it uses is the sum less the new value. It is as if
it hasn't been committed to the database by the time the recordset is
populated. So it is always one step behind. All I can think is to use an
update query to insert the value before loading the recordset to force the
value in there from the first. Is there a better way?

TIA,

Jarryd

That is some clever stuff. I haven't tried it yet. I have to get some
shut eye soon or I might as well just stay awake. But it all makes sense
but if you could please check bakc on this thread I would appreciate it.

TIA,

Jarryd

Hi Duane and Rick,

[quoted text clipped - 21 lines]
in what I assume, because it seems like quite a lot of work to
achieve something very simple. I hope I am wrong.

That is pretty much what you want to do. In some case you can have the
user enter start and end times and then use DateDiff() to get the seconds
difference with one simple expression.

How do you plan on tracking calls that originate at 11:59 pm and end at
12:01
am or later?

Suggestion: I've done this after much sweat and tears: store the begin
and
end times as Doubles. At 4 pm today, that would be 38617.66666. If the
call
lasts past midnight, the number left of the decimal (the 'days' portion)
will
be 38618, and the number to the right (the hours, minutes, and seconds
portion) will be whatever. To get the actual duration, subtract the two
numbers and multiply by 24 [Duration = 24*(EndTime - BeginTime)]. If you
want
to display it in hh:mm:ss format, it won't work if beyond 24 hours, as
you
already know. You'll probably get away with 27.032 hours faster than
getting
away with "hiding" 24 hours of phone usage. Besides, you could always
sweat
through the code of building a string with the exact format, as long as
you
don't use the format() function.

Example: say your addition of all durations comes out to 1.03439 (before
multiplication); cal it Tot. You can type

DayDur=Int(Tot): HrDur = Hour(Tot): MinDur = Minute(Tot): SecDur =
Second(Tot)


txtTotDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur) + ":" + CStr
(SecDur)

This should display 24:49:31

--
Sam


Message posted via AccessMonster.com

 

[Rick Brandt]

Hi Sam, Duane and Rick,

Well I have managed to get the data out and formait it correctly. What the
code is not doing is adding the correct data. I have set a
where clause that should filter the values to be added by the bill
number. When I run the query on its own it returns the correct
total. But in the event it adds up every value in the Durations
field. I don't understand it. If it goes in to debug mode I can
rest the cursor over the variables and they all come out correct,
except for Tot = rst!Sum. Bill = the correct bill number which is
passed in to the query, so why is it not filtering correctly? Please
see code below:
-----------------------------------------------------------------------------
Dim db As DAO.Database
Dim rst As DAO.Recordset

'Total of filtered Durations
Dim Tot As Double

'The number of the current bill stored in a hidden textbox, txtBillHdn
Dim Bill As String
Bill = Form_frmMobile_Bills.txtBillHdn

Set db = CurrentDb

'Recordset filtered by Bill
Set rst = db.OpenRecordset("SELECT Sum([UK>Int_Calls].Durations) AS
[Sum] FROM [UK>Int_Calls] WHERE ((([UK>Int_Calls].Bill)= '" & Bill &
"'));")
'Total equals sum of all values in Durations field, not filtered. This is a
problem!
Tot = rst!Sum
DayDur = Int(Tot): HrDur = Hour(Tot): MinDur = Minute(Tot): SecDur =
Second(Tot)
Me.txtIntCAllUKDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur)
+ ":" + CStr(SecDur)

Off hand I see no reason for the sum to not be picking up the correct amount.

-----------------------------------------------------------------------------

The other thing that I am thinking about is how do I make it possible
to do the reverse. I suppose it can't be that hard; I suppose use
Cast or something like that to concatenate the parts of the string
that will be used:

e.g. 3600 times the hours value, 60 * the minutes value, 1 * the
seconds value, and then add all three.

That would do it.

Am I on the right track? When do you think a datatype will be
created that stores time as an amount of time so that you don't have
to do all this? Honestly, it's an absolute nonsense.

Well we're up to 11 versions of Access without one so I wouldn't expect it any
time soon. I know of no other database that has a time duration data type
either. In most cases durations are over days or longer and are captured by
storing a start DateTime and an end DateTime. Then the duration is calculated
from the difference of those two.

The third thing I am having trouble with is getting the correct value
out of a recordset. I have want to add a value to a field
(CallCost), and on AfterUpdate it must tally up all the values in it
field (filtered by Bill, again). But the value it uses is the sum
less the new value. It is as if it hasn't been committed to the
database by the time the recordset is populated. So it is always one
step behind. All I can think is to use an update query to insert the
value before loading the recordset to force the value in there from
the first. Is there a better way?

Save the form's record first...

Me.Dirty = False

 

[OfficeDev18 via AccessMonster.com]

Hi Sam, Duane and Rick,

[quoted text clipped - 32 lines]

Me.txtIntCAllUKDur = CStr((DayDur * 24) + HrDur) + ":" + CStr(MinDur)
+ ":" + CStr(SecDur)

Off hand I see no reason for the sum to not be picking up the correct amount.
[quoted text clipped - 5 lines]

e.g. 3600 times the hours value, 60 * the minutes value, 1 * the
seconds value, and then add all three.

That would do it.

Am I on the right track? When do you think a datatype will be
created that stores time as an amount of time so that you don't have
to do all this? Honestly, it's an absolute nonsense.

Well we're up to 11 versions of Access without one so I wouldn't expect it any
time soon. I know of no other database that has a time duration data type
either. In most cases durations are over days or longer and are captured by
storing a start DateTime and an end DateTime. Then the duration is calculated
from the difference of those two.

The third thing I am having trouble with is getting the correct value
out of a recordset. I have want to add a value to a field

[quoted text clipped - 5 lines]

value before loading the recordset to force the value in there from
the first. Is there a better way?

Save the form's record first...

Me.Dirty = False

Jarry, just to start, I think your major problem is right in your SQL
statement:

Set rst = db.OpenRecordset("SELECT Sum([UK>Int_Calls].Durations) AS [Sum]
FROM [UK>Int_Calls] WHERE ((([UK>Int_Calls].Bill)= '" & Bill & "'));")

AS [Sum] must be changed; sum is a reserved word, meaning that it has special
meaning to the program (in this case, a function), and using ANY reserved
word as a variable, user function name or control name will get what IBM used
to call "unpredictable results." Change the name from Sum to something else,
such as SumOfDurations.