Is this the acceptable way to do a Function Procedure?

[PNGBill]

I have just done another Function Procedure which uses earlier functions to arrive at a final result. - It works.

Is the acceptable way or should I have made a long winded sql to gather the data again?

Public Function PortfolioBalance() As Currency
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim PortfolioSum As Currency    'Hold the Portfolio Balance as calculated
    
    PortfolioSum = 0                ' set variable to Zero to start
    
        'sql query to Sum TBLTRANS.TRNDR for Interest only up to and including today's date
       sqlString = "SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE TRNACTDTE<=Date() AND TRNTYP In (""Interest"");"
            
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    PortfolioSum = rst!SumOfTRNDR   'put result of sqlString (Interest to Date)as variable PortfolioSum
    
    PortfolioSum = PortfolioSum + GetRecordSums("TBLTRANS.TRNDR", "Late fee")
    
    PortfolioSum = PortfolioSum + GetRecordSums("TBLTRANS.TRNDR", "Legal Fees")
    
    PortfolioSum = PortfolioSum + GetRecordSums("TBLTRANS.TRNDR", "Principal")
    
    PortfolioSum = PortfolioSum + GetRecordSums("TBLTRANS.TRNDR", "Fee Aplic")
    
    PortfolioSum = PortfolioSum + GetRecordSums("TBLTRANS.TRNDR", "Fee Process")
    
    PortfolioSum = PortfolioSum - GetRecordSums("RepaymentsAll")
    
    PortfolioBalance = PortfolioSum     'Return Variable to Function PortfolioBalance
        
     
    'Close database variables
    rst.Close
    dbs.Close
       

End Function

 

[Galaxiom]

There is nothing wrong with calling one function or sub from another. Indeed it is often good structure to isolate code and control the passage of results. It also often facilitates the reuse blocks of code.

However I assume each time you call the function you are opening a recordset. This is going to be a lot slower than using a single query.

I haven't had a close look at your code but it looks to me that you could do it all in a query by adding the fee types to the where clause.

Where TRNACTDTE<=Date() AND TRNTYP In (""Interest"") AND TRNDR = 'Late fee' Or etc

I don't understand why you have used the In(""Interest"")

I would also recommend you normalize "Interest" and those fee types to Byte data types. It is much faster for Access to work with numbers than compare strings.

With careful allocation of the numbers used for feetypes you can pick the ones you want with a single numeric comparison in the Where clause.

 

[PNGBill]

Just noticed the copy database has grown by nearly 80mb
Could this be the caused by the opening record set you mention?

 

[Banana]

I do not believe that merely opening a recordset create bloat per se. Essentially, bloat is simply the fact that Access does not delete unused pages or re-allocate data to other pages during normal use... hence the need to do a compact & repair as a separate and dedicated operation. Easiest way to create bloat would be along the lines of using a lot of Make Table queries to create temporary tables, deleting them, appending a large amount of records, even to a permanent table then discarding them afterward. There's probably more. Of course, there's also bloat caused by designing & editing database objects but that's not the normal behavior one would observe out in the production.

Suggest you search the forum for keyword "Front end side end back end" (or should it be "Side-End"? There was a rousing thread involving myself, Galaxiom and few others in discussion of managing temporary data and the associated bloat.

Finally, I want to second Galaxiom's advice - I try to avoid string comparisons and if we can use a lookup table -- use it. To fully appreciate the reason, consider this: A string is basically an array of characters. Each characters, depending on character sets (aka codesets, code pages) being used can take between one to three bytes. Since 2000, Access has used Unicode by default so a character can consume 2 bytes per. Note that it also support Unicode compression, enabling us to use only 1 byte for the common characters and 2 byte for more exotic characters. Let's say we consume 1 byte per character. Compare this to long integer, the same data type used for Autonumber which is 32 bit or 4 bytes. So if you're comparing only four letters or less, it's on par (see caveat further down) with number comparisons. But do you always have four letters or less? Not likely - so more resources is required to do a comparison of a typical 3-8 letters word (recall that computers understand only binary so even characters are nothing but a string of binary code, no different from numbers.

Now, here's the caveat... character sets also comes with collation. Collation answers the question of how to compare two strings... We already know about "should "abc" = "ABC"?" but it's even more complicated... there's also a question of "should "Mueller" =
"Müller"?" (you would use German Phonebook collation to answer the question in affirmative). So there is a potential need to do two step in comparing two pieces of string. I would reasonably expect Access to optimize the 2nd step away since sort order is determined at database level but there may be no guarantee (and thus is necessary to take the 2nd step) if the string may come from somewhere else.

Using numbers has none of that overhead - an integer is always 4 bytes and there's no question about it. No need to size the array of characters, etc. etc. to do a compare - the hardware can go ahead and compare right away.

Now whether it'd be better to use a byte, I do not know... I understand that some hardware likes to align the oddly sized variables so a 32-bit processor may prefer to process byte as same as a four byte integer. Whether this has basis in fact, I don't know and would love to find out. If you want more nitty-gritty, suggest search for a thread asking about VBA Boolean data type being sized as 2 bytes instead of 1 byte while Yes/No data type are actually 1 byte instead of 1 bit.


Regarding the VBA code. I believe Leigh Purvis has mentioned to you in the other thread about the futility of dbs.Close method.

I also would like to see the code for earlier functions... I'm sure it's possible to express them as a single query.

HTH.

 

[PNGBill]

Thanks Banana for the every detailed explanation.

Regarding Bloat. I think the cause was I am working on a Macro that includes, as you mention above, makes 4 x temp tables and 1 x append query.
It did delete 2 off these before finishing and I cancelled the delete part as I worked on replacing the whole Macro with vba code and sql.
Running the macro a couple of times must have boated the database.
I compacted it and ran the function I made and no bloat.

I will work on One sql to cover the process being done by the function above.

You make sence regarding speed etc when handling data but it is annoying when you have to remember 1, 2 or three rather then Late fee, Legal Fees etc.

Our database is not slow and is about 350mb. Has been around for 10 years (started as 97, now 2000)

With the savings I am doing in VBA and Sql and reduction of the many temp tables, we should be able to reduce size and increase speed.

Could we keep Interest in the table but use an sql version to do all the heavy calc work. The version will substitute 1 for Interest ? or I guess we may as well just change the table??

From your story I guess up to 4 or 5 alpha chrs is ok, above that should be avoided.
We would use Alpha for Mr, Mrs, Ms, 3 digit state and country codes etc.

Will post some sql soon.

Thanks again for your assistance and advice.

 

[PNGBill]

Here is updated code that uses sql's

Public Function PortfolioBalance() As Currency          'Calculate Portfolio Balance as at today
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim PortfolioSum As Currency    'Hold the Portfolio Balance as calculated
    
    PortfolioSum = 0                ' set variable to Zero to start
    
        'sql query to Sum TBLTRANS.TRNDR for Interest, Late fee and Legal Fees only up to and including today's date
     sqlString = "SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE TRNACTDTE<=Date() AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"");"
            
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    PortfolioSum = rst!SumOfTRNDR   'put result of sqlString (Interest, Late fee and Legal Fees to Date)as variable PortfolioSum
    
        'sql to sum all TRNPR records in TBLTRANS which will pickup the Principal, Application Fees and Processing Fees
    sqlString = "SELECT Sum(TBLTRANS.TRNPR) AS SumOfTRNPR " & vbCrLf & _
            "FROM TBLTRANS;"
            
             'Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
        
    PortfolioSum = PortfolioSum + rst!SumOfTRNPR   'put result of sqlString (Principal, Fee Application and Fee Process)
                                                    'as addition to variable PortfolioSum
    
        'sql to Sum all PayAmt records in tblMemberRepayments - Sum of All Repayments ever made
    sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt " & vbCrLf & _
        "FROM tblMemberRepayments;"
        
          'Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
        
    PortfolioSum = PortfolioSum - rst!SumOfPaymentAmt    'deduct sum of Repayments from variable PortfolioSum
 
    PortfolioBalance = PortfolioSum     'Return Variable to Function PortfolioBalance
        
     
    'Close database variables
    rst.Close
    dbs.Close
       

End Function

Using 3 sql's as first sums TBLTRANS.TRNDR for the the 3 criteia in TRNTYP
2nd Sums TBLTRANS.TRNPR
and 3rd Sums tblMemberRepayments.PaymentAmt but in this case we want to deduct the sum from the two earlier sql results.

Could I have done this in 1 or 2 sql's ?

Have replaced In with =. In was a carry over from where the sql was used before.

yet to address field names.

 

[Banana]

This can be consolidated into a single query.

SELECT TOP 1 
Nz(
  (
    SELECT Sum(TRNDR) AS SumOfTRNDR
    FROM TBLTRANS
    WHERE TRNACTDTE<=Date() 
        AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"")
  )
, 0) + Nz(
  (
    SELECT Sum(TBLTRANS.TRNPR) AS SumOfTRNPR
    FROM TBLTRANS
  )
, 0) - Nz(
  (
    SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt
    FROM tblMemberRepayments
  )
, 0)
FROM MSysObjects;

Note:

We selected from MSysObjects as a workaround since this is one of cases where Access does not like it when we omit the FROM clause even though we actually don't need it... It's OK to do this:

SELECT 1;

or even

SELECT 1 + 1;

but when we use subqueries, Access insists on a FROM clause for the outer query even if we don't need it. Thus we select from a table that we can be certain will exist, is local so there's no wasted roundtrip to a server somewhere else. Thus MSysObjects is a good candidate. However, because MSysObjects can have more than one records, we add a TOP 1.

This is basically your 3 queries combined into a single big expression using 3 subqueries. Note that I've also added Nz(..., 0) to substitute the null results into zero so your calculation don't get nullified should any one of queries return zero records (thus null).

Of course, this query is ugly due to SQL being embedded in middle of function. I do imagine it can be prettied up by converting the SQL into a saved query and replacing the inner SQL with saved query's name. OTOH, this means one has to open up 3 other queries to figure out what the outer query is really doing.

As the saying goes - there's more than one way to skin a cat.

 

[PNGBill]

Thanks Banana.
Pasted sql into an sql query but it isn't happy.

Won't allow it to save it and highlights this opart if I try to run the query.

    SELECT Sum(TRNDR) AS SumOfTRNDR
    FROM TBLTRANS
    WHERE TRNACTDTE<=Date() 
        AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"")
  )

 

[Banana]

That's my bad - since it's now just a SQL statement and not a VBA, you no longer need to double up the quotes. So this should be:

("Interest","Late fee", "Legal Fees")

You needed the double quotes in VBA context since we were dealing with a VBA string and thus needed to embed the quote inside the string without closing the VBA string containing the SQL statement. But in a query, there's no need so you have to remove that.

HTH.

 

[PNGBill]

Thanks Banana, sql works. Will try and put into vba now.