Criteria on query w/ calculation acts like perameter query

[HLCruz via AccessMonster.com]

I have a query in which I am running a variety of calculations, many of which
are dependant on others. I am now trying to set a criteria for one of the
calculations - ([TotalCampFee]-[SumOfPayment]-[FinancialAsst]) AS BalanceDue -
I would like my query to only include those with a BalanceDue of greater than
zero.

When I try and set my criteria to >0 I get a perameter query pop up for
TotalCampFee. I imagine this is an easy fix, but I just can't seem to work
it out.

I appreciate anyone who is willing to help. Thanks, Heather


The entire query is pasted below:
SELECT tblCamperInformation.FirstName, tblCamperInformation.LastName,
tblCamperInformation.[Parent/Guardian], tblCamperInformation.Address1,
tblCamperInformation.Address2, EFM_ZIPS.CITY, EFM_ZIPS.STATE, EFM_ZIPS.ZIP,
tblCamperRegistration.CampYear, tblCamperRegistration.PaymentPlan,
tblCamperRegistration.Deposit, tblCamperRegistration.HorseCamp, IIf(
[HorseCamp]=True,90,0) AS HorseCampFee, IIf([CampYear]=2007,490,0) AS
07CampFee, ([07CampFee]+[HorseCampFee]) AS TotalCampFee, ([FirstName] & " " &
[LastName]) AS FullName, tblCamperRegistration.RegistrationID,
qryPaymentsTotals.SumOfPayment, tblCamperRegistration.FinancialAsst, (
[TotalCampFee]-[SumOfPayment]-[FinancialAsst]) AS BalanceDue, ("Dear " &
[Parent/Guardian] & ":") AS Salutation
FROM (EFM_ZIPS RIGHT JOIN (tblCamperInformation INNER JOIN
tblCamperRegistration ON tblCamperInformation.CamperID =
tblCamperRegistration.CamperID) ON EFM_ZIPS.ZIP = tblCamperInformation.Zip)
LEFT JOIN qryPaymentsTotals ON tblCamperInformation.CamperID =
qryPaymentsTotals.CamperID
WHERE (((qryPaymentsTotals.SumOfPayment) Is Not Null));

 

[John Spencer]

Unfortunately you need to redo the entire calculation in the where
clause. Calculated values in the select clause are not available in the
where clause since the records that are returned are computed before the
values that are returned.

That is the processing order for a SELECT query seems to be use the FROM
clause to get the tables and potential records.

Use the WHERE clause to filter the records

Use the select clause to return the fields and calculated values.



SELECT tblCamperInformation.FirstName, tblCamperInformation.LastName,
tblCamperInformation.[Parent/Guardian], tblCamperInformation.Address1,
tblCamperInformation.Address2, EFM_ZIPS.CITY, EFM_ZIPS.STATE, EFM_ZIPS.ZIP,
tblCamperRegistration.CampYear, tblCamperRegistration.PaymentPlan,
tblCamperRegistration.Deposit, tblCamperRegistration.HorseCamp
, IIf([HorseCamp]=True,90,0) AS HorseCampFee
, IIf([CampYear]=2007,490,0) AS 07CampFee
, ([07CampFee]+[HorseCampFee]) AS TotalCampFee
, ([FirstName] & " " & [LastName]) AS FullName
, tblCamperRegistration.RegistrationID
, qryPaymentsTotals.SumOfPayment
, tblCamperRegistration.FinancialAsst
, ([TotalCampFee]-[SumOfPayment]-[FinancialAsst]) AS BalanceDue
, ("Dear " &[Parent/Guardian] & ":") AS Salutation
FROM (EFM_ZIPS RIGHT JOIN (tblCamperInformation INNER JOIN
tblCamperRegistration ON tblCamperInformation.CamperID =
tblCamperRegistration.CamperID) ON EFM_ZIPS.ZIP = tblCamperInformation.Zip)
LEFT JOIN qryPaymentsTotals ON tblCamperInformation.CamperID =
qryPaymentsTotals.CamperID
WHERE qryPaymentsTotals.SumOfPayment Is Not Null

And
IIf([HorseCamp]=True,90,0) + IIf([CampYear]=2007,490,0) -
Nz([SumOfPayment],0) - Nz([FinancialAsst],0) > 0

Hopefully I've reconstructed the formula correctly. I've also wrappd
sumOfPayment and FinancialAsst in NZ, just in case they could be null
values.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query in which I am running a variety of calculations, many of which
are dependant on others. I am now trying to set a criteria for one of the
calculations - ([TotalCampFee]-[SumOfPayment]-[FinancialAsst]) AS BalanceDue -
I would like my query to only include those with a BalanceDue of greater than
zero.

When I try and set my criteria to >0 I get a perameter query pop up for
TotalCampFee. I imagine this is an easy fix, but I just can't seem to work
it out.

I appreciate anyone who is willing to help. Thanks, Heather


The entire query is pasted below:
SELECT tblCamperInformation.FirstName, tblCamperInformation.LastName,
tblCamperInformation.[Parent/Guardian], tblCamperInformation.Address1,
tblCamperInformation.Address2, EFM_ZIPS.CITY, EFM_ZIPS.STATE, EFM_ZIPS.ZIP,
tblCamperRegistration.CampYear, tblCamperRegistration.PaymentPlan,
tblCamperRegistration.Deposit, tblCamperRegistration.HorseCamp, IIf(
[HorseCamp]=True,90,0) AS HorseCampFee, IIf([CampYear]=2007,490,0) AS
07CampFee, ([07CampFee]+[HorseCampFee]) AS TotalCampFee, ([FirstName] & " " &
[LastName]) AS FullName, tblCamperRegistration.RegistrationID,
qryPaymentsTotals.SumOfPayment, tblCamperRegistration.FinancialAsst, (
[TotalCampFee]-[SumOfPayment]-[FinancialAsst]) AS BalanceDue, ("Dear " &
[Parent/Guardian] & ":") AS Salutation
FROM (EFM_ZIPS RIGHT JOIN (tblCamperInformation INNER JOIN
tblCamperRegistration ON tblCamperInformation.CamperID =
tblCamperRegistration.CamperID) ON EFM_ZIPS.ZIP = tblCamperInformation.Zip)
LEFT JOIN qryPaymentsTotals ON tblCamperInformation.CamperID =
qryPaymentsTotals.CamperID
WHERE (((qryPaymentsTotals.SumOfPayment) Is Not Null));