Printing reports a line at a time 1

[ronphx]

I am trying to print an aged accounts receivable report in Access. There are thousands of customers, and hundreds owe balances on account. How can I calculate each customer's current balance (from the invoice table and the payments table), then print a line on the report showing the customer's name and aged balance if he/she owes something, and skip the customers (don't print a line) who don't owe anything?

 

[scriverb]

You should create a query with both tables with their appropriate relationships and perform you calculations in the query. You see you can create new columns in the query based upong multiple records data and consolidate it into a single row. Then the query can select only those with a positive value.

This query then is used as the RecordSource for your report. It will only contain records that you want to print. If you need help setting this up post back with the details of your records, names, fields, linked fields, and i will help you develop a query that will work for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ronphx]

One of the problems I've found with putting the calculations in a query is that with many-to-many relationships the query replicates the table records from the invoice and payment tables. Actually, the VBA code to age the accounts is quite simple.

As an old (very old) dBase programmer I am used to being able to send items to the printer one line at a time. I realize this is a problem in a windows environment, but I have many reports where I make calculations using VBA code and wish to output the results as I make the calculations. Is there a VBA coding way to do this?

 

[scriverb]

Well I still think that a query is the answer for you. You can create a Function in a database module to just about analyze anything. It can be programmed to pass parameters from the current record in the query to the function and the function executes VBA code to rollup and calculate any value that you want.

You will result with a single row for the printing with a column of values. Which can then be analyzed for criteria selection.

Post back if you need more help developing this.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ronphx]

It sounds interesting. I would be interested to learn more about what you're suggesting. For simplicity, let's say I have three tables:

tblcustomer - with fields name and custnum

tblinvoice - with fields invoice, invoiceAmount, invoiceDate and custnum

tblpayment - with fields payAmount, payDate and custnum

How would I go about developing that function?

Thanks.

 

[scriverb]

I will show you two techniques.

1. The first is done completely within a query using ACCESS functions DSum to calculate the Invoice rollup amount and the Payments rollup amount.

Copy and save this SQL in a new query and name it appropriately.

Select C.Name, C.CustNum, DSum("InvoiceAmount", "tblInvoice", "[CustNum] = " & C.[CustNum]) as SumOfInvoices, DSum("PayAmount", "tblPayment", "[CustNum] = " & C.[CustNum]) as SumOfPayments, SumOfInvoices - SumOfPayments as AmountDue 
FROM tblCustomer as C 
WHERE (SumOfInvoices - SumOfPayments) > 0 
ORDER BY C.Name;

Run the above query and you should get the record source for your report.

2. This technique uses a query that calls up a Function to calculate the Amount Due for each customer:

Copy and paste this SQL code into a new query, name and save it:

Select C.Name, C.CustNum, CalcBalance(C.CustNum) as Balance_Owned 
FROM tblCustomer as C
WHERE CalcBalance(C.CustNum) > 0  
ORDER BY C.Name;

Copy and paste this function code into a new database module:

Public Function CalcBalance(vCustNum as Long) as Double
Dim db as DAO.Database, rs as DAO.Recordset
Dim strSQL as String, vBalnce as Double 
strSQL = "Select * FROM tblInvoice as I WHERE I.[CustNum]=" & vCustNum 
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
vBalance = 0
Do
   vBalance = vBalance + rs("InvoiceAmount")
   rs.MoveNext
Loop Until rs.EOF
rs.Close
strSQL = "Select * FROM tblPayment as P WHERE P.[CustNum]=" & vCustNum
Set rs = db.OpenRecordSet(strSQL, dbOpenDynaset)
rs.MoveFirst
Do
   vBalance = vBalance - rs("PayAmount")
   rs.MoveNext
Loop Until rs.EOF
CalcBalance = vBalance
rs.close
db.close
End Function

Now just run the above query and the recordset can be used as the Record Source for your Report.

I didn't provide two columns in the second query for the sum of invoice or the sum of payments. Didn't think it was required so left it out.

Post back with any questions. This is pseudo code so I may have a syntax error or something else but just post back with the problem and we can surely fix it.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ronphx]

Thank you for spending the time to write the above code. It looks like the second solution is the one I might pursue. Am I interpreting it correctly that the query is calling the function calcbalance for each customer, then the report is going to be based on each customer where the calcbalance > 0?

If so, I have some questions on the code:

1. You use C. what is C.?
2. Is C.Custnum the value that is passed to the function?
3. Can I return more than one variable from the function? For example, in a real application I would like to pass back to the query the total balance, the current balance, the over 30 days balance, the over 60 days balance and the over 90 days balance. I know how to calculate these amounts; I just need to know how to pass each one back to the query.

Thanks again.

 

[scriverb]

In answer to your questions:

1. C is just an Alias designation that I used in place of the table name tblCustomers in the Query. Just a shorthand so that you don't have to type or read tblCustomers over and over again. It makes it easier to read the code also especially if there are multiple tables linked through joins. Give them all a number and things read much better.

2. Yes, the function is setup with one parameter that is passed for each row of the query. We are calling the function and passing the tblCustomers field custnum to the Function. In the function code you will see a variable designated in the Function declaration . vCustNum. That variable is now used through the function code.

3. Functions return a value just like a variable value. At the end of a function VBA code you will see someplace the assignment of a final value to the function name. This is the value that is returned from the function call. For what you are asking you would have to create seperate functions to rollup different values. Now you could create Global variables at the module level that could get updated within a single Function call and then create a simple Function for each that just retrieves that Global variable value. This would save on the multiple openings and analysis of the records thus saving time in the query.

Post back if you have further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ronphx]

This is starting to come around. I found a TYPE statement which will return more than one value from a function, but I can't seem to get it to work in the query.

Any ideas?

 

[ronphx]

I gave up on the TYPE statement, as I don't think that was the correct way to go. I followed your suggestion to set up the global variables and call them from within the first function and it does seem to work fine.

Thank you for all your help. I will use your suggestion many times, I'm sure.