Solved QUERY - Data only from the last working week (1 Viewer)

[georg0307]

Dear All,

in a query how do I set data only from the last working week? i.e. Monday to Friday without putting dates.

I have this SQL but it doesn't work:
SELECT [2021].Date, [2021].ECO_LABEL, DatePart(weekday, [Date]) Between 2 And 6 AS Weekdays
FROM 2021;

Thanks in advance

best regards
Georg

 

[arnelgp]

you will need a function to return the last day of work done.
then move back to get the monday:

'arnelgp
Public Function fnStart() As Date
Dim d As Date
d = DMax("Date", "2021")
fnStart = FirstDayInWeek(d, vbMonday)
End Function

' https://stackoverflow.com/questions/30026572/how-to-find-the-start-date-of-week-from-a-given-date
Public Function FirstDayInWeek(ByVal dtmDate As Date, Optional FirstDay As VbDayOfWeek = 1)
FirstDayInWeek = dtmDate - Weekday(dtmDate, FirstDay) + 1
End Function

SELECT [2021].Date, [2021].ECO_LABEL
FROM 2021
WHERE [Date] Between fnStart() And fnStart()+5

 

[georg0307]

Ciao,
Fast & Furious (INCREDIBLE!), I will try.

 

[CJ_London]

Add this where clause to your sql

WHERE [Date]=> dateadd("ww",-1,date())-weekday(date(),2)+1 and [date]<=dateadd("ww",-1,date()-weekday(date(),2)+7

or

WHERE [Date] between dateadd("ww",-1,date())-weekday(date(),2)+1 and dateadd("ww",-1,date()-weekday(date(),2)+7

note that date is a reserved word (it means today) and should not be used as a field name - similarly it is not a good idea to name tables with a number

 

[ebs17]

@CJ_London: Jet cannot resolve the name of the VBA constant vbMonday, you would have to enter the value 2 directly.

 

[CJ_London]

oops tested in in the immediate window - code updated

 

[georg0307]

I receive back a message in Italian that the function is not defined what am i doing wrong?

 

[CJ_London]

you have a couple of solutions - which one are you referring to?

 

[ebs17]

that the function is not defined what am i doing wrong?

You'll probably also need to store the function in a standard module in your application to be able to use it. Just looking at the forum is not enough.

 

[georg0307]

Add this where clause to your sql

WHERE [Date]=> dateadd("ww",-1,date())-weekday(date(),2)+1 and [date]<=dateadd("ww",-1,date()-weekday(date(),2)+7

or

WHERE [Date] between dateadd("ww",-1,date())-weekday(date(),2)+1 and dateadd("ww",-1,date()-weekday(date(),2)+7

note that date is a reserved word (it means today) and should not be used as a field name - similarly it is not a good idea to name tables with a number

I receive this error

 

[georg0307]

you will need a function to return the last day of work done.
then move back to get the monday:

'arnelgp
Public Function fnStart() As Date
Dim d As Date
d = DMax("Date", "2021")
fnStart = FirstDayInWeek(d, vbMonday)
End Function

' https://stackoverflow.com/questions/30026572/how-to-find-the-start-date-of-week-from-a-given-date
Public Function FirstDayInWeek(ByVal dtmDate As Date, Optional FirstDay As VbDayOfWeek = 1)
FirstDayInWeek = dtmDate - Weekday(dtmDate, FirstDay) + 1
End Function

SELECT [2021].Date, [2021].ECO_LABEL
FROM 2021
WHERE [Date] Between fnStart() And fnStart()+5

I receive back this error

 

[Gasman]

View attachment 105926 I receive this error

So do you use ; instead of , for separators?

 

[Gasman]

I receive back this error View attachment 105927

And where have you saved fnstart() ?

 

[CJ_London]

looks like I missed a bracket in the second part - this is the revised code
Between DateAdd("ww",-1,date())-Weekday(Date(),2)+1 And DateAdd("ww",-1,Date())-Weekday(Date(),2)+7

 

[georg0307]

Hi,
I used this: Between DateAdd("ww",-1,Date())-Weekday(Date(),2)+1 And DateAdd("ww",-1,Date())-Weekday(Date(),2)+7))

Thanks to All, it works!!!