week numbers

[ukmale65000]

Ok week 1 will be 6th april to 12th april week 2 will be 13th april to 19th april and so on. The final week 52 or 53 depending on how many days are left. The final week ending on the 5th april may have anything between 1 and seven days.
Sorry if that makes things difficult.

[ItsMe]

That method will break within 5 years. If that is what you are after your formula will be to determine the difference in days and divide by 7. You could probably accomplish this in one line of code.

[ukmale65000]

5 years would be long enough

[ItsMe]

I have not tested this out past leap year. It seems to be working. I did not test it extensively.

DateDiff.zip

This DB has one form with three text boxes and a control button. It analyzes two dates. It uses the first date as the beginning of a period. It determines which period the second date falls into. Each period is equal to seven days.

Here is the code if anyone cares to critique it. It seems access does not offer built in symmetrical rounding so I used a function off of microsoft's KB.

   Function SymUp(ByVal X As Double, _
            Optional ByVal Factor As Double = 1) As Double
            
   Dim Temp As Double
   
     Temp = Fix(X * Factor)
     SymUp = (Temp + IIf(X = Temp, 0, Sgn(X))) / Factor
     
   End Function

'Evalute two dates and determine the period
'the second date falls within where seven
'days equals one period

Private Sub cmdDateDiff_Click()

Dim dtOne As Date
Dim dtTwo As Date
Dim intAnswer As Integer
Dim sglMath As Single
Dim intRound As Integer

dtOne = Me.txtOne.Value
dtTwo = Me.txtTwo.Value

intAnswer = DateDiff("d", dtOne, dtTwo) 'Count the number of days
intAnswer = intAnswer + 1  'Add the evaluation date back into the equation

sglMath = intAnswer / 7 'Measure days in units of 7

intRound = SymUp(sglMath) 'Round any fraction symmetrically

Me.txtAnswer.Value = intRound

End Sub

[ukmale65000]

Thanks for all your help i will try and incorporate this into my database, one more question and i may have to open a new thread for this but is it possible to create a report in a style where the user could have sort tabs along the top like those found when you run a query,and be able to click on any record and have it take you to the initial input form that the record was created on ?
Im not asking you to do any work for me just asking if this is possible ?

[ItsMe]

It is possible with newer versions of Access. It will require coding in VBA. It may be easier to do in a form vs. a report. You can lock down edit capabilities in a form. Forms in DS view have shortcut menus and search options built in that may do what you need.

As for the code pasted here... It is relatively copy/paste friendly. The top portion is a function. You will want to paste the entire function into a module. The other code you want to paste the sub into your own sub procedure. Create a sub for a control's event and THEN paste the code, minus the first and last line.

Of course, you will have to adjust names of controls, etc for the sub. Just be sure to paste the function as an entire function.