If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
automated statements
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. |
#2
|
|||
|
|||
automated statements
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 '================================================= === robertm600635 wrote: 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. |
#3
|
|||
|
|||
automated statements
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. "John Spencer" wrote: 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 '================================================= === robertm600635 wrote: 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. |
Thread Tools | |
Display Modes | |
|
|