Week number in access reports and query

[MrDeej]

Hello my good it people!

Since new year we have experienced a fault in our diagrams and reports in access. They show that we are in week 4, even when i let the diagram self make the week number (there is no place to define 'usesystem' in diagrams)

Is there a system setting (we use Windows XP SP2 with Access 2007) which covers this?

In some diagrams we only let access show week instead of date. But in other querys we use datepart() to get week number. Datepart i can manually set to datepart("WW";d ate();0;0) to get the right week. But then i have to update alot of querys and redesign those who use only the built-in function in the diagram.

So, is there a more easy solution to this?

[nico5038]

Guess the optimal solution should be found "out side" Access in the regional settings.There the week number is however not defined nor adjustable "straight away".
The only option I see there is the choice of a region using the week number as you need it. :-(

Personally I do use my own function to determine the week number (indeed with the Datepart function like: "DatePart(" ww", dtDateIn, vbSunday, vbFirstFourDays )") .
The Datepart(WW) can give a number 53 on the first days of January and combined with the year function that's introducing a possible erroneous YearWeeknumber, so my function corrects the year when necessary:

Function fncYearWeekString(dtDateIn As Date) As String
'Create year - week string for a given date with string format yyyy-ww,
'for making sure an alphanumeric sort is correct
'as ww returns no leading zero ! And correcting the year when first or last days are "over the edge"
If IsDate(dtDateIn) Then
    If DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays) < 10 Then
        If Month(dtDateIn) = 12 Then
            ' correction for last week of year when date falls in week of next year
            fncYearWeekString = Year(dtDateIn) + 1 & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
        Else
            fncYearWeekString = Year(dtDateIn) & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
        End If
    Else
        If Month(dtDateIn) = 1 Then
            ' correction for first week of year when date falls in week of previous year
            fncYearWeekString = Year(dtDateIn) - 1 & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
        Else
            fncYearWeekString = Year(dtDateIn) & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
        End If
    End If
Else
    fncYearWeekString = "*?*"
End If
End Function

By preparing a query with this YearWeeknumber I always get a "controlled " value for every date and a predictable result in graphs etc..

Nic;o)

[MrDeej]

Ok. I think i will make a code for this so that i can get away with only changing the code in one place if this is a problem in the future also

Thank you!

[nico5038]

See you're learning fast how to code properly :-)

Success with the application !

Nic;o)