Report calculations off

[mkelly]

I have a report that calculates "total pages" by group and then overall.

If I run 1/1/05 - 1/31/05 I get 10326 pages for report total
If I run 2/1/05 - 2/28/05 I get 11206 pages for report total
If I run 3/1/05 - 3/31/05 I get 9567 pages for report total

this totals 31,099 pages for the report totals combined

However if I run 1/1/05 - 3/31/05 I get 31,920 pages for the report total

a copy of the form in attached can antone tell me why this discrepency is happening??

 

[mkelly]

forgot attachment

sorry forgot attachment

 

[KenHigg]

Do your date flds happen to be capturing a time element as well as the date part? Example: Are you using Now() instead of date()?

Ken

 

[mkelly]

yes I am using NOW(), how can I change that to date in the query?

 

[KenHigg]

Date()


???
Ken

 

[mkelly]

I have thousands of records in the table that are NOW(), can I cange the NOW in the query to date()??

 

[KenHigg]

Do you want a technical answer as to the possible reason for the problem and/or a quick fix?

Ken

 

[mkelly]

at this point a quick fix, and then the technical answere when you have time.

Thank You

 

[KenHigg]

int(MyDateFld)


Ken

 

[mkelly]

So in the query would that loook like

expr1:int(job log in]![time qc totally complete])

??

 

[KenHigg]

Provided 'job log in' is the table and 'time qc totally complete' is the date/time fld.

Ken

 

[mkelly]

Sorry to waste so much of your tome but if i use:
Expr1: Int([job log in]![time qc totally complete])

in my query expr1: returns this output in that field

Expr1
37957
37931
37935
37935
37935
37937
38006
38006
38006
38006
38006
38006
38006
38006

 

[KenHigg]

Then you may need to do:

cdate(Int([job log in]![time qc totally complete]))

It should evaluate like it is...

Ken

 

[mkelly]

Thank You very much much appriciated just sorry I wasted so much of your time while I was sitting here having a brain fart..

 

[KenHigg]

Hold on, I have the follow up tech answer if you're interested...

Ken

 

[KenHigg]

For the following date / time values:

4/1/2005 12:54:33 PM
4/1/2005 12:54:34 PM
4/2/2005 12:54:35 PM
4/2/2005 12:54:36 PM
4/2/2005 12:54:37 PM


Access actually stores:

38443.5378819444
38443.5378935185
38444.5379050926
38444.5379166667
38444.5379282407


Everything on the left represents the 'day' values and everything on the right represents the 'time'.

So you can see that 38444 is the same as 4/2/2005.

Now if you said give me everything between 4/1/2005 and 4/2/3005, what you're actually saying is give me everything that is >= 4/1/2005 and <= 4/2/2005. This then would be the same as saying give me everything that is >= 38443 and <= 38444. Since the third date down in the example data is 38443.5378935185, it will not be included in the results dataset. Make sense?

So the solution is to start off with just the integer portion of the date/time fld using int().

Hence, the best long term solution is to just capture the date part to begin with with date() instead of capturing the date and time part with now()

(whew....)
Ken

 

[mkelly]

Always willing to learn more