Iif with 2 different sum formulas

[bernman]

Doing a simple group query, unfortunately "Duration" field has 2 different values minutes or hours. Want one value in the end "Hours".

If Payment Group = X then they need to be treated as Hours.
All others in Payment Group need are Minutes and converted to hours(divide by 60).

Hours:IIf([0001Initial_Extract_OT]!Pymt_Group="X",Sum([Duration]),Sum([Duration])/60)
:banghead:

 

[EternalMyrtle]

What is this part: [0001Initial_Extract_OT]!Pymt_Group="X" ?

If you are doing this in a query, your format for the field name should be

[FieldName] or [TableName].[FieldName] if necessary-- you would only need to use the latter if you had the same field name in two tables in your query.

Otherwise, it looks as if it should work.

 

[Mihail]

Try this:
Hours:IIf([0001Initial_Extract_OT]!Pymt_Group="X",Sum([Duration]),Sum([Duration]/60))

If will not work, create a query where to sum the durations then, based on this one, a query where to transform minutes in hours.

 

[bernman]

Thanks..
0001Initial_Extract_OT is the table
Pymt_Group is the field
The format was generated by the Expresiion builder
I'm getting the warning that I'm expression as part of an aggragate function

 

[EternalMyrtle]

Did you set the total field in the query to "Expression"?

 

[EternalMyrtle]

I just tested it and the total value does not have to be set to expression. You will get a data type mismatch error if it is set to something incompatible.

You could get the aggregate function error if the field has not been placed into the query. Did you make sure Pymt_Group was added to the query?

 

[EternalMyrtle]

([Duration]/60))

Good eye Mihail! I was looking for a wonky parenthesis but did not look carefully enough.

 

[wisenickel]

Does this work?

Hours:Sum(IIf([0001Initial_Extract_OT]!Pymt_Group="X",[Duration],[Duration]/60))

 

[bernman]

EternalMrytle, you win the prize!!
Pymnt_Group wasn't included.....
Thank you
Happy holidays