Expr1 Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my query I have two fields totaled in a field:
Expr1:format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"). I want to
be able to Group on the report by Job# and Sum the Expr1 field for each job.
The wizzard doesn't give me the option of Summary and when I create a text
box and enter
=Sum([Expr1]) I get the error message Data Type Mismatch in criteria
Expression. How do I correct this?
Thanks
 
Don't format your datediff in the query. Also, provide an adequate name for
your calculated column. Try:

HrsDiff:DateDiff("n",[Start Time],[Stop Time])/60
or
HrsDiff:Val(format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"))

Leaving "Expr1" in queries shouldn't be acceptable in any application.
 
I changed the name of the Expr1 field to Total Hours. However, the only
problem in the report was that there must be data in the field for the Sum
field to work.
Thanks!

Duane Hookom said:
Don't format your datediff in the query. Also, provide an adequate name for
your calculated column. Try:

HrsDiff:DateDiff("n",[Start Time],[Stop Time])/60
or
HrsDiff:Val(format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"))

Leaving "Expr1" in queries shouldn't be acceptable in any application.

--
Duane Hookom
MS Access MVP
--

Sam said:
In my query I have two fields totaled in a field:
Expr1:format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"). I want to
be able to Group on the report by Job# and Sum the Expr1 field for each
job.
The wizzard doesn't give me the option of Summary and when I create a text
box and enter
=Sum([Expr1]) I get the error message Data Type Mismatch in criteria
Expression. How do I correct this?
Thanks
Click to expand...
Click to expand...
 
I'm not sure what your last comment is referring to but I think you can use:
=IIf([HasData]=True, Sum([Total Hours]),0)

--
Duane Hookom
MS Access MVP


Sam said:
I changed the name of the Expr1 field to Total Hours. However, the only
problem in the report was that there must be data in the field for the Sum
field to work.
Thanks!

Duane Hookom said:
Don't format your datediff in the query. Also, provide an adequate name
for
your calculated column. Try:

HrsDiff:DateDiff("n",[Start Time],[Stop Time])/60
or
HrsDiff:Val(format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"))

Leaving "Expr1" in queries shouldn't be acceptable in any application.

--
Duane Hookom
MS Access MVP
--

Sam said:
In my query I have two fields totaled in a field:
Expr1:format(DateDiff("n",[Start Time],[Stop Time])/60,"fixed"). I want
to
be able to Group on the report by Job# and Sum the Expr1 field for each
job.
The wizzard doesn't give me the option of Summary and when I create a
text
box and enter
=Sum([Expr1]) I get the error message Data Type Mismatch in criteria
Expression. How do I correct this?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top