How to convert this excel formula to vba ?

[EmanuelSpence]

Hello I have a payroll week number formula

=INT((A1-DATE(YEAR(A1)-(A1<DATE(YEAR(A1),4,6)),3,30))/7)

Where A1 is the day you wish to find the week number for

How do i convert this formula to Access vba ?

Any help appreciated

 

[SkipVought]

hi,

'INT((A1-DATE(YEAR(A1)-(A1<DATE(YEAR(A1),4,6)),3,30))/7)
  dim DayVal, YourVal
'this assumes that DayVal as already been assigned
  YourVal = INT((DayVal-DateSerial(YEAR(DayVal)-([DayValue]-DateSerial(YEAR(DayVal),4,6)),3,30))/7)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWise]

for starters

datepart("ww",vardate)

 

[SkipVought]

OI! Week Number!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EmanuelSpence]

Thank you - nearly there

Can i put this in a text box expression so when a date is put in field (or box ) DayVal,
the value of YourVal (set up as a text box ) returns the value of the expression ?

Or in simple terms a person puts a date in box 1 and box 2 returns the number of the expression

 

[EmanuelSpence]

Sorry Skipval your formula does not work

In Excel the formula for 2/2/2011 returns 44

Your formula returns - 3296 ?

 

[SkipVought]

Exactly what is your formula calculating in your opinion?

First of all
[tt]
=INT((A1-DATE(YEAR(A1)-(A1<DATE(YEAR(A1),4,6)),3,30))/7)
[/tt]
evaluates as either TRUE or FALSE. Check it out using the Formula Evaluator feature!

Please explain what you expect THAT to do in the expression.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I see what's happening...

'INT((A1-DATE(YEAR(A1)-(A1<DATE(YEAR(A1),4,6)),3,30))/7)
  Dim DayVal, YourVal, off
'this assumes that DayVal as already been assigned
DayVal = CLng([A1].Value)
    If DayVal < DateSerial(Year(DayVal), 4, 6) Then
        off = 1
    Else
        off = 0
    End If
    YourVal = Int((DayVal - DateSerial(Year(DayVal) - (off), 3, 30)) / 7)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

it woudl actually be better to code as a function

Function MyWeek(dDate As Date) As Integer
    Dim off
    
    If dDate < DateSerial(Year(dDate), 4, 6) Then
        off = 1
    Else
        off = 0
    End If
    MyWeek = Int((dDate - DateSerial(Year(dDate) - off, 3, 30)) / 7)
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EmanuelSpence]

Thank you skip that works fine now

My next problem is that I have a table called "Short Time Working"

and the text box called "TaxNum" , how do I add the value of "TaxNum" to the table field "Week Number" in the table "Short Time Working" for teh particular record ShortTimeID I am working on

I wish it to update the "Week Number" record every time "TaxNum" changes

 

[SkipVought]

Please post a new thread addressing this new issue clearly and completely.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]