Filtering a report (2000)

Topic

New Reply

I would like know if it is possible to filter a report. I would like to set a limit on it so that if the returned value from a formula is zero, then the associated invoice or billing dates do not show.

The formula is [invoiceTotal] – sum[amountAllocated] The form is sorted by AccountID and DatePaid.

Thanks!

Leesha

Reply | Quote

Replies

Assuming that you open the report using DoCmd.OpenReport, you can specify a Where condition when opening the report. It would be best to include the balance (the result of the formula) in the Record Source of the report. Say that you name this field Balance and that the report is named rptMyReport; you could use

DoCmd.OpenReport “rptMyReport”, acViewPreview, , “Balance 0″

If you already had a Where condition, concatenate the conditions with ” And “, for example

DoCmd.OpenReport “rptMyReport”, acViewPreview, , “AccountID = ” & Me.AccountID & ” And Balance 0″

Reply | Quote

Thanks Hans, I’ll give this a shot tonight!

Leesha

Reply | Quote

Hi Hans,

I tried the first version of the code and got the following error message “You tried to execute a query that does not include the specified expression ‘CustomerID’ as part of an aggregate function.” I haven’t as of yet written a where condition but that will be coming so I appreciate your giving me the code!!

The SQL for the data source for the report is ……………..

SELECT tblPayment.CustomerID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, [Salutation] & ” ” & [FirstName] & ” ” & [MiddleInitial] & ” ” & [LastName/BusinessName] & ” ” & [suffix] AS Expr1, [amount]-Sum([amountallocated]) AS Balance
FROM (tblDemographics INNER JOIN tblPayment ON tblDemographics.AccountID = tblPayment.CustomerID) INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID;

Thanks!
Leesha

Reply | Quote

Try this, it may satisfy your requirements, it might not:

SELECT tblPayment.AccountID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, [Salutation] & ” ” & [FirstName] & ” ” & [MiddleInitial] & ” ” & [LastName/BusinessName] & ” ” & [suffix] AS AcctName, [amount]-Sum([amountallocated]) AS Balance
FROM tblDemographics INNER JOIN (tblPayment INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID) ON tblDemographics.AccountID = tblPayment.AccountID
GROUP BY tblPayment.AccountID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, [Salutation] & ” ” & [FirstName] & ” ” & [MiddleInitial] & ” ” & [LastName/BusinessName] & ” ” & [suffix];

Reply | Quote

OK, so now I’m really in awe. It works exactly like I was looking for. Initially I had a few errors and some parameter issues, but I figured those out on my own (will wonders never cease) by making the necessary changes to the report and now its all set to go!!!

Hope the house hunting is coming along!

Thanks again,
Leesha

Reply | Quote