Oh so simple query - but it ain't working! Calculated fields.

[Stan Smith]

I have 2 columns in my telephone call database - call_start and Call_end.
They contain the appropriate times in hours and minutes.
In the query I wish to find out the duration of the calls - easy peasy -
I enter Durn: [call_end]-[call_start] in the field of the query . Test it
and it produces a column of figures - however the first one (should be 00:30
minutes {09:15-08:45}) is 0.0208333333 and looking down the column they all
bear no resemblence to the times they should be.
Just to complicate things if I analyse the database table in Excel and ask
it to do the same subtraction the answers come out correctly!!!.
where oh where am I going wrong.

Please help this pitiful wretch that once was a man - before Access!

Stan

 

[Guest]

Hi Stan,

Access stores date/time values as numbers, where the integer part refers to
the day, and the fraction refers to the time as a fraction of a day.

So, the result that you are getting is correct, 30 minutes is .02083 of a
day (30/60/24).

You can either multiply the result by 24*60, or you can use the DateDiff
function to do what you want.

HTH, Ted Allen

 

[Stan Smith]

Ted - THANK YOU - the veil has lifted from my watery eyes!!!
Would have been going round in circles for a long while to come over this.
These Newsgroups are a real boon for newbies like me.
All the very best
Stan

Hi Stan,

Access stores date/time values as numbers, where the integer part refers to
the day, and the fraction refers to the time as a fraction of a day.

So, the result that you are getting is correct, 30 minutes is .02083 of a
day (30/60/24).

You can either multiply the result by 24*60, or you can use the DateDiff
function to do what you want.

HTH, Ted Allen

I have 2 columns in my telephone call database - call_start and Call_end.
They contain the appropriate times in hours and minutes.
In the query I wish to find out the duration of the calls - easy peasy -
I enter Durn: [call_end]-[call_start] in the field of the query . Test it
and it produces a column of figures - however the first one (should be 00:30
minutes {09:15-08:45}) is 0.0208333333 and looking down the column they all
bear no resemblence to the times they should be.
Just to complicate things if I analyse the database table in Excel and ask
it to do the same subtraction the answers come out correctly!!!.
where oh where am I going wrong.

Please help this pitiful wretch that once was a man - before Access!

Stan

 

[Guest]

This will produce the difference in minutes.

DateDiff("n",[Table1].[call_start],[Table1].[Call_end])

Hope this helps.

 

[Guest]

My pleasure Stan. Glad it helped.

Ted - THANK YOU - the veil has lifted from my watery eyes!!!
Would have been going round in circles for a long while to come over this.
These Newsgroups are a real boon for newbies like me.
All the very best
Stan

Hi Stan,

Access stores date/time values as numbers, where the integer part refers to
the day, and the fraction refers to the time as a fraction of a day.

So, the result that you are getting is correct, 30 minutes is .02083 of a
day (30/60/24).

You can either multiply the result by 24*60, or you can use the DateDiff
function to do what you want.

HTH, Ted Allen

I have 2 columns in my telephone call database - call_start and Call_end.
They contain the appropriate times in hours and minutes.
In the query I wish to find out the duration of the calls - easy peasy -
I enter Durn: [call_end]-[call_start] in the field of the query . Test it
and it produces a column of figures - however the first one (should be 00:30
minutes {09:15-08:45}) is 0.0208333333 and looking down the column they all
bear no resemblence to the times they should be.
Just to complicate things if I analyse the database table in Excel and ask
it to do the same subtraction the answers come out correctly!!!.
where oh where am I going wrong.

Please help this pitiful wretch that once was a man - before Access!

Stan