Criteria of Calculated field

[rmoore]

I have a query "qAmountOwed" with a calculated field [RemainingBalance]. I need to set the criteria to show the records that are greater than $1.



I have tried: >1

>"1"

>

$0.00

>"

$0.00"

Nothing seems to work

Also I cant figure out why the "Enter the Parameter" box opens when I run it.

Any suggestions?

[June7]

Can't reference an aggregate field in the same query it is calculated. Try:

SELECT tLoans.LoanNumber, tLoans.AmountOwed, Sum(tPayments.PaymentAmount) AS SumOfPaymentAmount, [AmountOwed]-Sum([tPayments].[PaymentAmount]) AS RemainingBalance, [FirstName] & " " & [LastName] AS FullName
FROM (tCustomers INNER JOIN tLoans ON tCustomers.CustomerID = tLoans.CustomerID) INNER JOIN tPayments ON tLoans.LoanNumber = tPayments.LoanNumber
GROUP BY tLoans.LoanNumber, tLoans.AmountOwed, [FirstName] & " " & [LastName]
HAVING ((([AmountOwed]-Sum([tPayments].[PaymentAmount]))>0))
ORDER BY [FirstName] & " " & [LastName];

Why is PaymentAmount field in multiple tables?

[rmoore]

I will change that. PaymentAmount in tLoans records the payment "expected/required" calculated from the loan amount, number of payments and interest. The "other" PaymentAmount is to record the actual payment made, I use that one to calculate the remaining balance.

I realize there are many ways to structure the DB to accomplish the same thing. Do you see a better way of doing this to simplify other aspects?

[June7]

Okay, would just suggest to make names slightly different. Don't use exactly same field name in multiple tables.

[rmoore]

Thanks June7... Where do I use the code you suggested?

[June7]

It's a query. It is a modification of the query you built. Switch to SQLView to see SQL statement. Copy/paste mine. Then switch to DesignView.

[rmoore]

Thanks..... worked like a charm.... and it fixed the paramiter value box from popping up