2

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.

Improve this question
3
  • Make sure you have an index on EffectiveDate and/or create one query that incorporates the functionality of fncVAT. Commented Sep 15, 2020 at 6:35
  • 1
    You can use ozh.github.io/ascii-tables to get tabular data into formatted tables for posting. Commented Sep 15, 2020 at 8:05
  • @Gustav I placed and index on the EffectiveDate but no change still takes long to load. Commented Sep 15, 2020 at 19:54

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is how to do it, but you can leave the table as is and use this query in its place: SELECT TaxTable.EffectiveDate, Nz((Select Min(EffectiveDate) From TaxTable As T Where T.EffectiveDate > TaxTable.EffectiveDate),#12/31/9999#) AS EndDate, TaxTable.VAT FROM TaxTable;
0

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.

Improve this answer

2 Comments

OP doesn't need the current tax rate, but for each product row the tax rate that applies to its Purchase date.
Oh I get it, the problem arises when this function is applied to older purchases. For current purchases, my solution should work. I will modify my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.