Can't Sum a Calculated Field in a Chart? 2

[JSD]

Hello,

I am working with a query that has a calculated field that subtracts two date fields to get its value. I want the sum of this value in a chart with 'date' as a baseline. However, I am having trouble getting msaccess to do anything but count the records for the calculated field. I get an error message that says I can't Sum date, text, key values. The calculated field is the difference of two dates, but doesn't appear to be in the date format (it results in a number). Also, I changed the format of the calculated field in the query to 'General Number', and it still won't let me sum the value in the chart. Here is the expression in the calculate3d field:

act setup hrs cnc: IIf([workcenter]="CNC" And [insu]=-1 And [down]=0;([timeout]-[timein])*24;"")

Can I put the format (General Number) in this expression somewhere so msaccess will recognize it is a number result that can be summed? Any help is greatly appreciated.

Many thanks

Jeremy

 

[mgolla]

Jeremy,

Have you tried this:

act setup hrs cnc: Format(IIf([workcenter]="CNC" And [insu]=-1 And [down]=0;([timeout]-[timein])*24;""),"General Number")


HTH
Mike

 

[Golom]

Rather than attempting direct arithmetic on date fields, use the DateDiff function.
[tt]
DateDiff ("h", [timein],[timeout])
[/tt]
And you want the FALSE condition to be Zero (not an empty string as represented by "").

 

[JSD]

Thanks guys