Totals expression in query using the OR line in criteria

[Richard Harison]

My database involves volunteers. One may be a primary volunteer [vol1] or
secondary volunteer [vol2]. Any volunteer whether primary or secondary
accumulates kilometers driven. I have a report which needs to total kms driven,
regardless of whether it is the primary volunteer [vol1km] or the secondary
volunteer [vol2km]. The report starts with a dialog asking for the name of the
volunteer. I pass the selection to the query and I use the OR line, since the
volunteer in any given record will be [vol1] OR [vol2]. I now need to display
the kms driven per occurrence (i.e. record) in the report. Since one is either
a primary OR secondary volunteer, in the query I created an expression field
=[vol1km] & [vol2km] it works. (I realize that concatenation is not the best
way to fly.)
Problem is--in the report I need a grand total of all Kms driven by the given
volunteer regardless of whether primary or secondary. I know from experience
that Access is easily confused when you try to evaluate expressions within the
report, and I have always "done the math" in the underlying query. But this
time nifty expressions like expr1:=[vol1km]+[vol2km] plain don't work. (I
assume the OR criteria is somehow the culprit here. Any ideas? Thanks

 

[Squirrel]

Hi Richard,

Since you want to generate totals per volunteer why do you need to reference
the [vol1] or [vol2] attribute?

Linda

 

[Richard Harison]

The report is grouped by volunteer name regardless of whether he was a primary
or secondary volunteer. I need the KM totals regardless of whether primary or
secondary. Thanks

--
All the Best
Richard Harison

Hi Richard,

Since you want to generate totals per volunteer why do you need to reference
the [vol1] or [vol2] attribute?

Linda

My database involves volunteers. One may be a primary volunteer [vol1] or
secondary volunteer [vol2]. Any volunteer whether primary or secondary
accumulates kilometers driven. I have a report which needs to total kms
driven, regardless of whether it is the primary volunteer [vol1km] or the
secondary volunteer [vol2km]. The report starts with a dialog asking for the
name of the volunteer. I pass the selection to the query and I use the OR
line, since the volunteer in any given record will be [vol1] OR [vol2]. I now
need to display the kms driven per occurrence (i.e. record) in the report.
Since one is either a primary OR secondary volunteer, in the query I created
an expression field =[vol1km] & [vol2km] it works. (I realize that
concatenation is not the best way to fly.)
Problem is--in the report I need a grand total of all Kms driven by the given
volunteer regardless of whether primary or secondary. I know from experience
that Access is easily confused when you try to evaluate expressions within
the report, and I have always "done the math" in the underlying query. But
this time nifty expressions like expr1:=[vol1km]+[vol2km] plain don't work.
(I assume the OR criteria is somehow the culprit here. Any ideas? Thanks

 

[Richard Harison]

I figured it out---an IIF statement in a report control did the trick.

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George