CODEWITHSUNDEEP
c#ms-access-2007
IrfanRaza
IrfanRaza

Reputation: 3058

Trouble with MS Access Query

I am using MS Access 2007 along with a C# application. Following is the query that I'm currently using. This query is working fine:

string qry = "Select sum(InvoiceAmount) as InvAmt, InvoiceDate From Invoices Group By InvoiceDate Having (InvoiceDate between #"+previousPaymentDate+"# and #"+currentPayDate+"#)";

The above query is returning the sum of invoice amounts group by invoice date.

There are some invoices generated due to cheque bounce. i.e. say 25000/- is the invoice amount for cheque bounce. I have marked this invoice using "ChequeBounced" boolean field. Is there any way to include this clause within above query. I want this query somewhat like this -

 string qry = "Select sum(InvoiceAmount) as InvAmt, InvoiceDate From Invoices Group By InvoiceDate Having (InvoiceDate between #"+previousPaymentDate+"# and #"+currentPayDate+"#) and ChequeBounced='False'";

Thanks for sharing your time.

Upvotes: 0

Views: 264

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

How about:

SELECT InvoiceDate, Sum(InvoiceAmount) AS InvAmount
FROM Invoices
WHERE ChequeBounced=False
GROUP BY InvoiceDate
HAVING (InvoiceDate between #"+previousPaymentDate+"# and #"+currentPayDate+"#)

I have not laid out the SQL with quotes for clarity. Note that False is a built-in constant equal to zero in Access and should not be quoted for boolean fields. It would be best to ensure that your dates are in the format yyyy/mm/dd, or you may get unexpected results.

Upvotes: 2

Related Questions