Trying to return Week number starting from Monday in Report

[Davism4]

Hi

I am out my depth with this, but my report is working with this line. The trouble is runs Sunday to Saturday. I would like to get the week day to start on Monday.



How can I change this SQL line so it runs Monday to Sunday ??

WHERE (((Year([Date Locked Out])*53+DatePart("ww",[Date Locked Out]))=Year(Date())*53+DatePart("ww",Date())-1))


Thanks for your help,

[moke123]

Have you tried including the "First Day of Week" argument?

DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]])

https://docs.microsoft.com/en-us/off...epart-function

[Davism4]

I've been trying to incorporate it into the expression, but without luck so far !

[moke123]

Sorry but your expression makes no sense to me.
What is it your trying to do?

[Gicu]

As suggested:

WHERE (((Year([Date Locked Out])*53+DatePart("ww",[Date Locked Out],vbMonday))=Year(Date())*53+DatePart("ww",Date(),vbMonday)-1))

[Davism4]

Thanks Vlad, I have tried this but I just end up with a pop up box for enter parameter value: vbMonday

For a different angle , If I look at design view, it is :

Year(Date())*53+DatePart("ww",Date())-1

Any other ideas?

[Davism4]

Hi, I am simply trying to produce a report of the previous weeks data, running Monday - Sunday.
My code is working but it is running Sunday - Saturday !

[Gicu]

Try using the actual value (2) instead of the built in constant name:

WHERE (((Year([Date Locked Out])*53+DatePart("ww",[Date Locked Out],2))=Year(Date())*53+DatePart("ww",Date(),2)-1))

[Davism4]

Vlad, that works! Brilliant thank you so much

[Gicu]

You're very welcome!