Form DSum Calculation for Tax Year

[mtagliaferri]

I have a text box to display the Year To Date Total and I am using

=DSum("TaxPaid","tblPayslip")

This gives me the total of all tax paid within the table 'tblPaylip'

I have to filter the Dsum between financial year based on 'PayslipDate'
e.g. if e'PayslipDate' = 15/09/2024 the date range would be from 06/04/2024 to 05/04/2025 included or if if 'PayslipDate' = 06/06/2023 the date range would be from 06/04/2023 to 05/04/2024 included

What is the DSum formula?

Thanks

 

[Pat Hartman]

Your example doesn't make sense. BUT, if your dates are strings, make sure they are in standard US date format mdy or the unambiguous ymd. If the expression will reference control fields that are defined as dates, you should NOT format your dates.

 

[arnelgp]

what is the name of the field for the range 06/04/2024 to 05/04/2025 and 06/04/2023 to 05/04/2024?
maybe you need a custom function do do the summing.

 

[mtagliaferri]

what is the name of the field for the range 06/04/2024 to 05/04/2025 and 06/04/2023 to 05/04/2024?
maybe you need a custom function do do the summing.

Apologies I should have provided more details, I use date in the format DD/MM/YYYY.
There is no field associated with the dates mentioned, it is based on the 'PayslipDate' field, the UK financial year runs from 06 April every year to 06 April the following year.
So the calculation is based on the 'PayslipDate'
E.G.
20/MAR/2024
15/APR/2024
12/MAY/2024
10/JUN/2024
08/JUL/2024
05/AUG/2024
01/SEP/2024
30/SEP/2024

So if PayslipDate is 30/SEP/2024 it should include all the above records with exclusion of the first one as the the date range would be from 06/APR/2025 to PayslipDate

Basically a Sum of Year to Date starting from the start of the specific financial year to the date of the payslip.

I am trying to achieve this without a query and purely with an AfterUpdate function which displays the result on the form.
The table has a field 'TaxPaid' when a new record is entered, as soon as the field 'Taxpaid' is populated the the 'YTDTaxPaid' field is updated based on the Control Source where I placed the formula '=DSum("TaxPaid","tblPayslip")'

I created a dummy field in the form 'TaxDate' and in the AfterUpdate on the PayslipDate with

Me.TaxDate = DateSerial(Year(PayslipDate), 4, 5)

This returns the start date for the financial year so for PayslipDate 30SEP2024 I will get 06APR2024.
So the DSUM should calculate between the range TaxDate and PayslipDate.

Now I have realised that any PayslipDate from January the following year to 05APR should still fall under TaxDate 06APR2024 but I get a date 2025 for the January-March records.

I believe I may be too optimistic being able to do this without queries....

 

[Gasman]

I would write my own function and pass in what year you require.
Then do all the hard work in that function. I say hard, but just mean the lines of code, as the calculation would be easy.

 

[xavier.batlle]

Perhaps this example helps you to find the range: (Updated version)

 

[arnelgp]

i have the same table as in post #6.
see DemoForm form on the attached.
see the AfterUpdate of the combobox of the form for the Calculation of Total taxes.

 

[Gasman]

Try this in your DSum()

Function UKTaxYear(dtDate As Date) As Integer
Dim iYear As Integer

iYear = Year(dtDate)

If dtDate <= DateSerial(Year(dtDate), 4, 5) Then
    iYear = iYear - 1
End If
UKTaxYear = iYear
End Function

Minimally tested.

 

[gemma-the-husky]

It would be interesting to know what fields you have.

Given that a payslip once produced would never be altered I would be inclined to include some cumulative values in the payslip record, such as "total tax paid in this employment",which is re-evaluated as you produce each pay slip. It's a useful figure to have available, I imagine.

This would be relevant whether it not tax is calculated on a Month 1/week 1 basis.

I imagine the payslip would also have a tax year and period counter, so that in the UK for example October 2024, would show as year 2024, period 7 (or year 2025 depending how you designated your years.

You might have a check process to confirm the figure agrees with the payslips for the tax year.

This is my view for UK payroll. I am not familiar with what would make sense in other countries payrolls.

You could just not store the cumulatives and generate the totals by adding all the details for tax year 2024 or 2025.

Even if the system does not expose cumulatives to users, I can still imagine it as a useful thing to store.

Doing the payroll based on the tax year/month numbers means the actual date is not significant for these purposes.

@mtagliaferri
Looking at your dates, it looks you have 13 x 4:weekly payments. Do you use 52 tax periods in that case, so you pay based on tax weeks 4,8,12 etc, rather than use 12 monthly pay periods? Just wondering.

Maybe UK tables allow for 13 pay periods. I'm not sure offhand. I can only remember seeing weekly and monthly examples from my many years experience, though. My government pension is paid in 13 4-weekly instalments, but there's never any deductions from that.

 

[mtagliaferri]

Perhaps this example helps you to find the range: (Updated version)
View attachment 116494

Thanks for the help with this.

I still not sure of the formula to be places in the field control source to have the sum
of tax starting by From to PayslipDate

 

[xavier.batlle]

Thanks for the help with this.

I still not sure of the formula to be places in the field control source to have the sum
of tax starting by From to PayslipDate
View attachment 116500

Perhaps you find this example useful. (Not fully testeted)

 

[arnelgp]

another demo.