UK tax week

[haylau]

Hi

I need to be able to calculate UK tax week at various places throughout the database, and I cam upon this coding from Gary (BidME) in another post, and it is exactly what i need

But being very new to VBA coding where do I start

How / where do I enter the code in the first instance?

Then how do I "call" the function in a form, report and a query

many thanks for help

Ron

Gary's code:

BidME (Programmer)
4 Mar 03 12:43
Try adding this sub routine:
It works by looking for the first week in April with a monday and using that as week 1. I hope this is ok.
It will calculate this depending on the year.
i.e 21/04/03 = wk 3
as 07/04/03 = wk 1

07/01/2003 = wk 41
as 01/04/2002 = wk 1



Replace all date_tb with you date text box (DateRec)
and all wk_no_tb with you week no display box (WeekNo)

At any pont you can then say "Call calculate_tax_week"
This will insert the tax week into the text box.


---------------------------------------------------
Public Sub calculate_tax_week()

On Error GoTo Err_calculate_tax_week

Dim week_no, day_of_week As Integer
Dim search_date As Date
Dim year, date_string As String

week_no = 0
day_of_week = 0
date_string = "01/04/"
If DatePart("m", date_tb) >= 1 And DatePart("m", date_tb) < 4 Then
'start of tax year is in previous year
year = CStr(DatePart("yyyy", date_tb) - 1)
Else
'if in april and before new tax date use previous year
If DatePart("m", date_tb) = 4 Then
search_date = date_tb
Do
If DatePart("w", search_date) = 2 Then
year = CStr(DatePart("yyyy", date_tb))
Exit Do
Else
search_date = search_date - 1
year = CStr(DatePart("yyyy", date_tb) - 1)
End If
Loop Until DatePart("m", search_date) < 4
Else
year = CStr(DatePart("yyyy", date_tb))
End If

End If

search_date = CDate(date_string + year)
Do
If DatePart("w", search_date) = 2 Then
'first Monday of April
week_no = 1
day_of_week = 1
Else
search_date = search_date + 1
End If
Loop Until week_no = 1

search_date = search_date - 1
day_of_week = day_of_week - 1

Do
search_date = search_date + 1
day_of_week = day_of_week + 1
If day_of_week = 8 Then
day_of_week = 1
week_no = week_no + 1
End If
Loop Until search_date = date_tb

wk_no_tb = week_no

Exit_calculate_tax_week:
Exit Sub

Err_calculate_tax_week:
MsgBox Err.description
Resume Exit_calculate_tax_week

End Sub


--------------------------------------------

Works nicely when I tested it.

Gary

 

[dhookom]

I would rather not try to figure out the code. Can you describe what the UK tax week is? Is this a single week or a week number based off some starting point in the year?

Duane
Hook'D on Access
MS Access MVP

 

[haylau]

Thanks for the offer of help dhookom, but I am now sorted