Can I put Criteria in the sum of a report?

[Walter Steadman]

I have a query that lists all the pilots in the unit and all the hours they
have flown. One of the fields in the Query is called firstSemi and it is a
Yes/No checkbox. one of the other fields in the query is Hours. Is it
possible in the report to put an unbound textbox and do something like
if([firstsemi] = true then Sum(hours))? I know I should do this in the
query portion, but trying to find out if it is possible in the report
section. That way I can put two unbound textboxes and one will give me the
sum for when the firstsemi checkbox is true and one will give me the total
of hours if the check is false. Don't know how to make it work in a query
because in a query it does not include all pilots at all times. If a pilot
has no checkmarks as true then he/she is not included in the query results
so therefore won't be in teh report results either.

Thanks in advance for any assistance

Wally Steadman
US Army, Iraq

 

[Ken Snell [MVP]]

Yes, in general you can use an IIf statement in an unbound textbox to
calculate a value.

 

[Walter Steadman]

This is what I have
iif([1stsemi] = true, sum([hoursflow]),"0")

I have also tried it with quotes around true

I get the following error:
Syntax error in query expression 'First([iif([1stsemi] = true,
sum([hoursflown]),"0")])

I have the report grouped by Pilot. I am using Access 2003. From the looks
of the error, is there something wrong with the query? The query really
doesnt have any criteria it just combines two tables that are linked via a
relationship. I note that it says First at the beginning of the statement
above and I don't know where that comes from. Any help would be appreciated

Yes, in general you can use an IIf statement in an unbound textbox to
calculate a value.


--

Ken Snell
<MS ACCESS MVP>

I have a query that lists all the pilots in the unit and all the hours
they have flown. One of the fields in the Query is called firstSemi and
it is a Yes/No checkbox. one of the other fields in the query is Hours.
Is it possible in the report to put an unbound textbox and do something
like if([firstsemi] = true then Sum(hours))? I know I should do this in
the query portion, but trying to find out if it is possible in the report
section. That way I can put two unbound textboxes and one will give me
the sum for when the firstsemi checkbox is true and one will give me the
total of hours if the check is false. Don't know how to make it work in a
query because in a query it does not include all pilots at all times. If
a pilot has no checkmarks as true then he/she is not included in the query
results so therefore won't be in teh report results either.

Thanks in advance for any assistance

Wally Steadman
US Army, Iraq

 

[Duane Hookom]

You need to decide if your fields are named "firstSemi" or "1stSemi" OR
"HoursFlown" or "HoursFlow" . Try use an expression like:
=Sum( Abs([FirstSemi] = True) * [HoursFlown])

--
Duane Hookom
MS Access MVP

This is what I have
iif([1stsemi] = true, sum([hoursflow]),"0")

I have also tried it with quotes around true

I get the following error:
Syntax error in query expression 'First([iif([1stsemi] = true,
sum([hoursflown]),"0")])

I have the report grouped by Pilot. I am using Access 2003. From the
looks of the error, is there something wrong with the query? The query
really doesnt have any criteria it just combines two tables that are
linked via a relationship. I note that it says First at the beginning of
the statement above and I don't know where that comes from. Any help
would be appreciated

Yes, in general you can use an IIf statement in an unbound textbox to
calculate a value.


--

Ken Snell
<MS ACCESS MVP>

I have a query that lists all the pilots in the unit and all the hours
they have flown. One of the fields in the Query is called firstSemi and
it is a Yes/No checkbox. one of the other fields in the query is Hours.
Is it possible in the report to put an unbound textbox and do something
like if([firstsemi] = true then Sum(hours))? I know I should do this in
the query portion, but trying to find out if it is possible in the report
section. That way I can put two unbound textboxes and one will give me
the sum for when the firstsemi checkbox is true and one will give me the
total of hours if the check is false. Don't know how to make it work in
a query because in a query it does not include all pilots at all times.
If a pilot has no checkmarks as true then he/she is not included in the
query results so therefore won't be in teh report results either.

Thanks in advance for any assistance

Wally Steadman
US Army, Iraq

 

[Walter Steadman]

Thanks alot Duane et all,
That last suggestion did the trick. I certainly appreciate it. I was
at a sticking point and now I can progress forward a bit.

Wally Steadman
US Army in Iraq

You need to decide if your fields are named "firstSemi" or "1stSemi" OR
"HoursFlown" or "HoursFlow" . Try use an expression like:
=Sum( Abs([FirstSemi] = True) * [HoursFlown])

--
Duane Hookom
MS Access MVP

This is what I have
iif([1stsemi] = true, sum([hoursflow]),"0")

I have also tried it with quotes around true

I get the following error:
Syntax error in query expression 'First([iif([1stsemi] = true,
sum([hoursflown]),"0")])

I have the report grouped by Pilot. I am using Access 2003. From the
looks of the error, is there something wrong with the query? The query
really doesnt have any criteria it just combines two tables that are
linked via a relationship. I note that it says First at the beginning of
the statement above and I don't know where that comes from. Any help
would be appreciated

Yes, in general you can use an IIf statement in an unbound textbox to
calculate a value.


--

Ken Snell
<MS ACCESS MVP>

message I have a query that lists all the pilots in the unit and all the hours
they have flown. One of the fields in the Query is called firstSemi and
it is a Yes/No checkbox. one of the other fields in the query is Hours.
Is it possible in the report to put an unbound textbox and do something
like if([firstsemi] = true then Sum(hours))? I know I should do this in
the query portion, but trying to find out if it is possible in the
report section. That way I can put two unbound textboxes and one will
give me the sum for when the firstsemi checkbox is true and one will
give me the total of hours if the check is false. Don't know how to
make it work in a query because in a query it does not include all
pilots at all times. If a pilot has no checkmarks as true then he/she is
not included in the query results so therefore won't be in teh report
results either.

Thanks in advance for any assistance

Wally Steadman
US Army, Iraq