Results 1 to 6 of 6

Thread: Global function

  1. #1
    Join Date
    Mar 2011
    Posts
    7

    Unanswered: Global function

    I need to be able to calculate UK tax week at various places throughout the database, and I came 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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply create an independant module (in the Database windows, select "Modules" --> "New". A new (blank) window opens. Paste the code of the function there, then in the "Tools" menu select "Compile". After a very short moment the code in your database should be compiled. Notice that you'll only receive a message if an error occurs during the compilation process: No message tells you "Compilation done" or "OK", etc...

    You'll probably need to transform the code as it is, from a Sub to a Function that will accept Input value (the date of reference) and will return a result (the week number). With that done you'll be able to use the function from everywhwere in the database. e.g. (here in the module of a form):
    Code:
    ' Text_Week_Number is a TextBox control in the current form
    '
    Me.Text_Week_Number.Value = calculate_tax_week(<Some date>)
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    Simply create an independant module (in the Database windows, select "Modules" --> "New". A new (blank) window opens. Paste the code of the function there, then in the "Tools" menu select "Compile". After a very short moment the code in your database should be compiled. Notice that you'll only receive a message if an error occurs during the compilation process: No message tells you "Compilation done" or "OK", etc...

    You'll probably need to transform the code as it is, from a Sub to a Function that will accept Input value (the date of reference) and will return a result (the week number). With that done you'll be able to use the function from everywhwere in the database. e.g. (here in the module of a form):
    Code:
    ' Text_Week_Number is a TextBox control in the current form
    '
    Me.Text_Week_Number.Value = calculate_tax_week(<Some date>)
    Thanks Sinndho - I am over half way there i think
    Sto 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?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Just after the line:
    Code:
    wk_no_tb = week_no
    Add this line:
    Code:
    calculate_tax_week = week_no
    That's it.
    Have a nice day!

  5. #5
    Join Date
    Mar 2011
    Posts
    7
    You're a star

    Cheers


  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •