UK Tax Week on a form 1

[HighLightUK]

I am looking to put the UK tax week on a form so it displays automatically when a user creates a new record.

There is a catch however, as the field WeekNo must first look at the date the record was entered (the record may be for last week for example)in a DateRec field.

How do I get the WeekNo field to convert the date entered by the user in the DateRec field into the Tax week number of the year?

 

[KenReay]

Hi

The easiest way to do this, although it requires some user input once a year, it to have table of taxweeknumbers and weekending dates. You can then derive the tax week number via a simple look up Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BidME]

Hi

Easier than that.
Have another text box with the DateRec field as the Control Source.
Under Format choose 'ww'. This will convert the date to the week number.
Alternatively use Datepart("ww", yourdate)

Regards,
Gary

 

[HighLightUK]

Hi Guys,

Thanks for the replies. Help is always appreciated.

Ken, I was hoping for a way to do this without involving the users. They are REAL pains and I want them to maintain as little as possible other than the main data they input. Previously, I had them entering the week number themselves (as they have it provided to them on a form for input), but they still would like it automating to save time.

Gary, I understand where you are coming from, but this approach will only give me the week number of the year. I need 7th April 2003 to be week 1, not week 15.

Thanks,

Steve

 

[KenReay]

Hi

OK, in that case, you need a variation on GAry's method to computer the week number based on the week number reurned by the 'standard' function. But I would have thought, taht unless you have access to the Inland Revenue algirithm, you will have problems keeping things synchronised (week 53 and all that). Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BidME]

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(&quot;m&quot;, date_tb) >= 1 And DatePart(&quot;m&quot;, date_tb) < 4 Then
'start of tax year is in previous year
year = CStr(DatePart(&quot;yyyy&quot;, date_tb) - 1)
Else
'if in april and before new tax date use previous year
If DatePart(&quot;m&quot;, date_tb) = 4 Then
search_date = date_tb
Do
If DatePart(&quot;w&quot;, search_date) = 2 Then
year = CStr(DatePart(&quot;yyyy&quot;, date_tb))
Exit Do
Else
search_date = search_date - 1
year = CStr(DatePart(&quot;yyyy&quot;, date_tb) - 1)
End If
Loop Until DatePart(&quot;m&quot;, search_date) < 4
Else
year = CStr(DatePart(&quot;yyyy&quot;, date_tb))
End If

End If

search_date = CDate(date_string + year)
Do
If DatePart(&quot;w&quot;, 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