Viewing data by current week

[cjm77]

I'm a bit new at all this but am trying to produce a query that filters on dates within the current week. Is there any way of doing this? I now how to do it for the day but at the moment I'm having to manually input 2 dates to get data in between them. Is there a command that will automatically detect the week and pull up dates within that?

 

[ColinEssex]

Look up Weekday() in access help. This tells you how to find FirstDayOfWeek, then from that you can find out the last day

Col

 

[cjm77]

thanks Col but I've looked on help and it's not very clear. I have a load of data with dates and want to automatically filter it by week so that it is always current. Short of manually inputing 2 dates to filter data between I haven't found another way of doing it. I need something that has the same effect as Date() but for week.

 

[Jon K]

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

will return records between Sunday and Saturday.
.

 

[ray147]

I am trying to do something similar..

however I want the user to input the week number and the system should output the first date of that week...

for example, user inputs week number 7, system outputs 12/02/2006 (UK) or 02/12/2006 (US).

I have read through various date functions such as DatePart, DateDiff, Weekday but didn't manage to find a solution...

has anybody did this?!

 

[ray147]

is this impossible guys?

 

[raskew]

Hi -

Take a look at: http://www.access-programmers.co.uk/forums/showthread.php?t=102310

Bob

 

[ray147]

thanks for that Bob..

currently this is what i have...but i'd like to input the week number ex: week number 7, and i'd like the system to output 12/02/2006 (UK) or 02/12/2006 (US).


can't figure this one out!

 

[Jon K]

....I want the user to input the week number and the system should output the first date of that week...

A public function to return the first day of a week number.

Public Function getFirstDay(WeekNum, Optional iYear) As Date
    Dim YearStart As Date
    
    If IsMissing(iYear) Then
       iYear = Year(Date)
    End If
    YearStart = DateSerial(iYear, 1, 1)
    
    If WeekNum = 1 Then
       getFirstDay = YearStart
    Else
       getFirstDay = YearStart - Weekday(YearStart) + 1 + (WeekNum - 1) * 7
    End If
    
End Function

You can pass a week number and an optional year number to the function. If a year number is not supplied, the current year is assumed e.g.

Both getFirstDay(7) and getFirstDay(7, 2006) return 12/02/2006 (UK) or 02/12/2006 (US)

and getFirstDay(7, 2005) returns 06/02/2005 (UK) or 02/06/2005 (US)
.