MS Access VBA Function causing query load slowly

user_11115377 0
Viewed 74

I need some guidance in making a code a little more efficient.

Public Function fncVAT(ByVal tDate As Variant) As Variant

Dim strTDate As String
Dim strVat As String

tDate = CDate(Nz(tDate, 0))

strTDate = "#" & Format(tDate, "mm/dd/yyyy") & "#"
strVat = "SELECT TOP 1 Vat FROM Tax WHERE [EffectiveDate] <= " & strTDate & _
                " ORDER BY [EffectiveDate] DESC;"

    With CurrentDb.OpenRecordset(strVat)
        If Not (.BOF And .EOF) Then
            fncVAT = .Fields(0)
        End If
    End With

End Function

What it does, it applies the current VAT rate from the Tax Table based on the date the product is purchased. For example

Tax Table

+----------------+-------+
| Effective Date |  VAT  |
+----------------+-------+
| 9/1/17         | 15%   |
| 2/1/19         | 12.5% |
+----------------+-------+

Purchases Query

+----------+---------+------+---------+---------------------------------------------------+
| PurDate  | Product | Cost | Vatable |                        Vat                        |
+----------+---------+------+---------+---------------------------------------------------+
| 1/31/18  | Cola    |   70 |       0 | IIf([Vatable]=0,[Cost]*fncVAT([PurDate]),0)=10.50 |
| 12/28/19 | Cola    |   70 |       0 | IIf([Vatable]=0,[Cost]*fncVAT([PurDate]),0)=8.75  |
| 5/3/20   | Flour   |   15 |      -1 | IIf([Vatable]=0,[Cost]*fncVAT([PurDate]),0)=0     |
+----------+---------+------+---------+---------------------------------------------------+

I've narrowed it down to this function. The query has about 900 records and it takes a while to load. Any suggestions? Thanks in advance.

ms-access vba
2 Answers

Probably the easiest way is to modify your VAT table, so it has not only the starting date but also the end date of each tax period.
Of course the EndDate of the current rate is not known yet, so use the max value there.

+-----------+------------+-------+
| StartDate |  EndDate   |  VAT  |
+-----------+------------+-------+
| 9/1/17    | 2/1/19     | 15%   |
| 2/1/19    | 12/31/9999 | 12.5% |
+-----------+------------+-------+

Then you can find the matching VAT row with a JOIN in your query, and no longer need the function:

SELECT p.PurDate, p.Product, p.Cost, p.Vatable,
  IIf(p.Vatable=0, p.Cost * v.VAT, 0) AS Vat
FROM tblProduct p INNER JOIN Vat v 
ON (p.PurDate >= v.StartDate) AND (p.PurDate < v.EndDate)

This should give decent performance.

Note the >= and < (not <=) in the join.
So to fill the EndDate column, you can simply copy the block of StartDate one row up.

user_38202710

Why do you need the ORDER BY? ORDER BY is generally quite slow. I think that you might want to get the most recent tax rate. So the ORDER BY is here because you want to get the tax rate that currently applies. What I personally would do is creating a new column in the tax table that tells us whether the tax rate in a given row is the currently effective tax rate. This could be done by factoring out the ORDER BY clause into a new query which is run only on demand (on change of effective tax rates). Doing it every time you query the effective tax rate is unnecessary.

The query might look like this: first, create a new column in table Tax called "currently_effective" (can be a Number field), which indicates that the tax rate in a given entry is the most recent one, the one to be applied. Then, from a VBA script, execute two queries. The first one sets all values in Tax.[currently_effective] to 0 (False), so that old values do not hold the currently effective tax rate. Then, set Tax.[currently_effective] to 1 (True) where the entry has the most recent date.

UPDATE Tax
SET Tax.[currently_effective] = 0

UPDATE Tax
SET Tax.[currently_effective] = 1
WHERE Tax.[EffectiveDate] =
(
SELECT TOP 1 EffectiveDate
FROM Tax
WHERE [EffectiveDate] > @SOME_VALUE
ORDER BY [EffectiveDate] DESC
)

In the fncVAT you could then just do SELECT Vat FROM Tax WHERE Tax.[currently_effective] = 1 Also, to get the most recent Vat I think you should use '>' for the date, but I'm not exactly sure what you're trying to achieve.

EDIT:

OP, I misunderstood what you are asking. My solution only works for new purchases. To apply the tax rate to existing purchases I second Andre's solution. To me, your function should only be applied irregularly to already existing purchase records, so I don't get why it is a problem if the function is slow. The speed would only impede database performance if you use the function regulary for new records, in which case my solution still applies.

user_118559070
Related