Expression on a report

[oxicottin]

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.

 

[Duane Hookom]

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

 

[oxicottin]

I tried it in a text box control source and it brought back an error? any
other ideas?

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

--
Duane Hookom
Microsoft Access MVP

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.

 

[Duane Hookom]

Tried what? What was the Control Source? What section of the report? "brought
back an error"... what error?

--
Duane Hookom
Microsoft Access MVP

I tried it in a text box control source and it brought back an error? any
other ideas?

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

--
Duane Hookom
Microsoft Access MVP

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.

 

[oxicottin]

Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text
box control source on the Reports Page Footer. When I ran the report It asked
me for a Date like it is supposed to and when the report came up the text
box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error.
There was no pop up or anything like that it just said error in the text box.

Tried what? What was the Control Source? What section of the report? "brought
back an error"... what error?

--
Duane Hookom
Microsoft Access MVP

I tried it in a text box control source and it brought back an error? any
other ideas?

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

--
Duane Hookom
Microsoft Access MVP


:

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.

 

[Duane Hookom]

The text box needs to be in a Report or Group Header or Footer section. You
can't easily aggregate records in Page sections.

--
Duane Hookom
Microsoft Access MVP

Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text
box control source on the Reports Page Footer. When I ran the report It asked
me for a Date like it is supposed to and when the report came up the text
box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error.
There was no pop up or anything like that it just said error in the text box.

Tried what? What was the Control Source? What section of the report? "brought
back an error"... what error?

--
Duane Hookom
Microsoft Access MVP

I tried it in a text box control source and it brought back an error? any
other ideas?

:

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

--
Duane Hookom
Microsoft Access MVP


:

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.

 

[oxicottin]

Thanks a million!!! I was putting it in the wrong spot!!!!

The text box needs to be in a Report or Group Header or Footer section. You
can't easily aggregate records in Page sections.

--
Duane Hookom
Microsoft Access MVP

Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text
box control source on the Reports Page Footer. When I ran the report It asked
me for a Date like it is supposed to and when the report came up the text
box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error.
There was no pop up or anything like that it just said error in the text box.

Tried what? What was the Control Source? What section of the report? "brought
back an error"... what error?

--
Duane Hookom
Microsoft Access MVP


:

I tried it in a text box control source and it brought back an error? any
other ideas?

:

You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum])
In your case, this would be
=Sum(Abs([Shift]=1) * [Employee Regular Time])

--
Duane Hookom
Microsoft Access MVP


:

I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is
12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound
text box show the sum of each Employee Regular Time by shift. I tried writing
a query that was bound to my initial query. The only problem is that there
was criteria place in the initial query. When I got to the report, The report
asked for Date 4 times (I believe because of initial query and then 3
shifts.) Any help would be appreciated.