Create global function to calculate tax week

[haylau]

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 forum, 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

[pbaldy]

Here's a start:

http://www.baldyweb.com/Function.htm

Personally, I'd pass the date to the function and have it return the result, rather than relying on fixed textboxes. That way you can use it anywhere.

[haylau]

Here's a start:

http://www.baldyweb.com/Function.htm

Personally, I'd pass the date to the function and have it return the result, rather than relying on fixed textboxes. That way you can use it anywhere.

Thanks, so to change to a function I just change:
Public Sub calculate_tax_week()

to
Public function calculate_tax_week(date_tb)
and that will then accept any date I supply for the calculation

I think I have that covered, but how to return the result?

The sub has wk_no_tb = week_no
how to I get the function to return and display that week number?

[pbaldy]

You'd add the declaration:

Public function calculate_tax_week(date_tb As Date) AS Integer 'or other appropriate data type

and instead of setting the value of a textbox at the end you set the return value of the function:

calculate_tax_week = week_no

[haylau]

That's great. Cheers