CODEWITHSUNDEEP
sqlms-access
iKindOfCode
iKindOfCode

Reputation: 29

Access SQL SELECT * WHERE Weeknum<CurrentWeek AND Year<=CurrentYear

I am sure i am missing something obvious, but i cant figure this out. My problem is very simple i have a table that contains the following information:

Year, Weeknumber, Data

What i want is to SELECT only data where the weeknumber is less that the current week. That is easy. But what i really want is all data that is registered before the current weeknumber AND current year. Meaning if i have the following data:

Year   Week   Data
2019    20    123
2019    21    234
2020    20    345
2020    21    456

If the current week is 21 of 2020 then i want the output to be:

year   Week   Data
2019    20     123
2019    21     234
2020    20     234

I dont want 2020 - 21 because that is the current year/week My current query is:

SELECT T.Year, T.Week, T.Data
FROM tblData AS T
WHERE Year<=year(Date()) AND Week<Weeknumber(Date())

The problem with this query is that it doesnt show weeks above 21 in 2019 which i want it to do. I know why this sql doesnt work, but cant figure out how to do it correctly.

How do i do this?

Upvotes: 1

Views: 72

Answers (3)

June7
June7

Reputation: 21370

Year is a reserved word and should not use reserved words as names for anything. I used Yr as field name.

Consider:

SELECT Yr, Week, Data
FROM Table1
WHERE [Week] Between 1 And IIf([Yr]=Year(Date()), DatePart("ww", Date()) - 1, 53);

Upvotes: 1

Gustav
Gustav

Reputation: 55831

As you are from Denmark, the table will most likely reflect years and weeks according to ISO 8601. However, no native function of VBA will return the ISO 8601 year and week of a date, and - for dates around New Year - the year will often differ from the calendar year.

Thus, functions to return the ISO 8601 year and week are needed:

    Public Const MaxWeekValue           As Integer = 53
    Public Const MinWeekValue           As Integer = 1
    Public Const MaxMonthValue          As Integer = 12
    Public Const MinMonthValue          As Integer = 1


' Returns the ISO 8601 week of a date.
' The related ISO year is returned by ref.
'
' 2016-01-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Week( _
    ByVal Date1 As Date, _
    Optional ByRef IsoYear As Integer) _
    As Integer

    Dim Month       As Integer
    Dim Interval    As String
    Dim Result      As Integer

    Interval = "ww"

    Month = VBA.Month(Date1)
    ' Initially, set the ISO year to the calendar year.
    IsoYear = VBA.Year(Date1)

    Result = DatePart(Interval, Date1, vbMonday, vbFirstFourDays)
    If Result = MaxWeekValue Then
        If DatePart(Interval, DateAdd(Interval, 1, Date1), vbMonday, vbFirstFourDays) = MinWeekValue Then
            ' OK. The next week is the first week of the following year.
        Else
            ' This is really the first week of the next ISO year.
            ' Correct for DatePart bug.
            Result = MinWeekValue
        End If
    End If

    ' Adjust year where week number belongs to next or previous year.
    If Month = MinMonthValue Then
        If Result >= MaxWeekValue - 1 Then
            ' This is an early date of January belonging to the last week of the previous ISO year.
            IsoYear = IsoYear - 1
        End If
    ElseIf Month = MaxMonthValue Then
        If Result = MinWeekValue Then
            ' This is a late date of December belonging to the first week of the next ISO year.
            IsoYear = IsoYear + 1
        End If
    End If

    ' IsoYear is returned by reference.
    Week = Result

End Function


' Returns the ISO 8601 year of a date.
'
' 2016-01-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function YearOfWeek( _
    ByVal Date1 As Date) _
    As Integer

    Dim IsoYear As Integer

    ' Get the ISO 8601 year of Date1.
    Week Date1, IsoYear

    YearOfWeek = IsoYear

End Function

An example:

? Week(#2024/12/31#)
 1 

? YearOfWeek(#2024/12/31#)
 2025

Now, use these in your query:

SELECT 
    T.Year, 
    T.Week, 
    T.Data
FROM 
    tblData AS T
WHERE 
    T.Year * 100 + T.Week < YearOfWeek(Date()) * 100 + Week(Date())

And you can safely keep Year as the field name as long as you prefix it in queries with the table name as you already do or wrap it in brackets: [Year].

Upvotes: 1

iKindOfCode
iKindOfCode

Reputation: 29

After further testing, June7 was very close, but that sql ended up removing the weeks greater than current week in 2019 (Which i didnt want). I might not have been clear enough in my question, but here is the sql WHERE criteria that gave me what i wanted. Which is, i only want the query to remove data from the current week in the current year. everything older than that, i want shown.

WHERE tbl.Week Between 1 And IIf(tbl.Yr=Year(Date()),Datepart("ww",Date())-1,53)

but thanks for your help @June7, since you guided me straight to the answer

Upvotes: 0

Related Questions