automated statements

[Guest]

I have a report set up that shows an individual client's billing statement.
Is there a way I could set up a command button to automatically print
statements for all client's that havent made a payment in the last 60 days?
To print individual statements i use the Where clause in the vba code to
print only the current record. The where clause uses the client's file number
field.

 

[John Spencer]

Yes there is. You haven't given us any details on your table structure,
so it is a bit difficult to say what the solution is.

Perhaps a query to select the correct records and then base your report
on the query.

Two query solution.
Get clients that have made a payment in the last 60 days.

SELECT ClientID
FROM PaymentsTable
WHERE PaymentDate > DateAdd("d",60, Date())

Save that a q60DayPays
Now use that in a second query (unmatched query wizard) to identify
those that are not in the list of 60 day payers.

SELECT ClientTable.*
FROM ClientTable
WHERE ClientTable LEFT JOIN q60DayPays
ON ClientTable.ClientID = q60DayPays.ClientID
WHERE q60DayPays.ClientID is Null


Otherwise, post your code to print the single report and some details on
the query that you use for the single report.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Ok I have a main table ClientInfo and a related table ClientFees (one-to-many
using the ClientID field from the main table).

The code for printing a single report:

Private Sub cmdPrintStatement_Click()
On Error GoTo Err_cmdPrintStatement_Click

Dim stWhere As String

stWhere = "[infFileNumber] = '" & Me.txtFileNumber & "'"


DoCmd.OpenReport "rptFeeStatement", , , stWhere

Exit_cmdPrintStatement_Click:
Exit Sub

Err_cmdPrintStatement_Click:
MsgBox Err.Description
Resume Exit_cmdPrintStatement_Click
End Sub

The query for the report has fields from both tables and the PaymentDate
field has criteria using the Between..And function to show only records in a
range of dates that the user defines on an unbound form. the PaymentDate
field also has the Totals row set to "Max" so it only displays the most
recent payment date. I want to be able to only show records that have Max
PaymentDates older than 60 days. Also, we write-off some fees if there are no
payments in a long time, say 2 years. I would like to be able to filter out
any records that are written off. Each payment or fee record also has a field
called Category which would be where "Write-Off" would be selected. Thanks.