Select query works until I put in criteria

[Steve]

I have the following query which works fine until I try to put a criteria in
the Balance field. I am trying to only return records that have a balance

0. When I put the criteria in, I am prompted for InvoiceTotal.

This is the query that works without the criteria:

SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater,
[InvoiceTotal]+[TotalPayments] AS Balance,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
DSum("PaymentAmount","tblPayments","[InvoiceId] =" &
[tblInvoices.InvoiceId]) AS TotalPayments
FROM tblInvoices INNER JOIN tblPayments ON tblInvoices.InvoiceId =
tblPayments.InvoiceId;

This is what Access gives me when I put in a criteria:

SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater, [InvoiceTotal]+[TotalPayments] AS Balance,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
DSum("PaymentAmount","tblPayments","[InvoiceId] =" & [tblInvoices.InvoiceId])
AS TotalPayments
FROM tblInvoices INNER JOIN tblPayments ON tblInvoices.InvoiceId =
tblPayments.InvoiceId
WHERE ((([InvoiceTotal]+[TotalPayments])>0));

TIA for your help.

Steve

 

[Steve]

As is usually the case for me, I figured it out after I posted the question.
The solution I came up with is this:

SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater, [InvoiceTotal]+[TotalPayments] AS Balance,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
DSum("PaymentAmount","tblPayments","[InvoiceId] =" & [tblInvoices.InvoiceId])
AS TotalPayments
FROM tblInvoices INNER JOIN tblPayments ON tblInvoices.InvoiceId =
tblPayments.InvoiceId
WHERE
((([tblInvoices.InvoiceLeaseAmount]+[tblInvoices.InvoiceElectric]+[tblInvoices.InvoiceWater]+(DSum("PaymentAmount","tblPayments","[InvoiceId]
=" & [tblInvoices.InvoiceId])))>0));

Essentially redoing the calculations I had as InvoiceTotal and TotalPayments.

Steve

I have the following query which works fine until I try to put a criteria in
the Balance field. I am trying to only return records that have a balance

0. When I put the criteria in, I am prompted for InvoiceTotal.

This is the query that works without the criteria:

SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater,
[InvoiceTotal]+[TotalPayments] AS Balance,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
DSum("PaymentAmount","tblPayments","[InvoiceId] =" &
[tblInvoices.InvoiceId]) AS TotalPayments
FROM tblInvoices INNER JOIN tblPayments ON tblInvoices.InvoiceId =
tblPayments.InvoiceId;

This is what Access gives me when I put in a criteria:

SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater, [InvoiceTotal]+[TotalPayments] AS Balance,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
DSum("PaymentAmount","tblPayments","[InvoiceId] =" & [tblInvoices.InvoiceId])
AS TotalPayments
FROM tblInvoices INNER JOIN tblPayments ON tblInvoices.InvoiceId =
tblPayments.InvoiceId
WHERE ((([InvoiceTotal]+[TotalPayments])>0));

TIA for your help.

Steve