Week commencing day from week number

[sdawson]

I have a query that groups records by date into week numbers using the following expression.

WeekNumber: DatePart("ww",[Date],2)

I would prefer to display the result as the week commencing Monday date.

How?

 

[grnzbra]

Use the Weekday function along with the DateAdd function to get Monday's date.

Public Function MonDt(Dt As Date) As Date
Dim DtIn As Date
Dim DOW As Byte

DtIn = Dt
DOW = Weekday(DtIn, vbMonday)

MonDt = DateAdd("d", -DOW + 1, Date)

If you are trying to do it as a calculated field in a query, I think it would be something like

MonDt: DateAdd("d", -WeekDay(DateField, vbMonday) + 1, DateField)

 

[sdawson]

Looks good.
Thanx.

 

[sdawson]

Is it me?

I've put the following expression in expression builder in the Query

Expr1: Date1 + (7 + vbMonday - DatePart("w", Date1 )) Mod 7

and it immediately changes to

Expr1: Date1 + (7 + [vbMonday] - DatePart("w", Date1 )) Mod 7

When I run the Query it asks for a value for [vbMonday].

Same with MonDt: DateAdd("d", -WeekDay(DateField, vbMonday) + 1, DateField) as per grnzrba's reply

and any variation.

Where am I going wrong?

 

[RuralGuy]

Here's a link you might find useful: A plethora of date functions

 

[grnzbra]

Sorry about that. I didn't try that last expression. I use the vba function. You could write it as a public function and set the field equal to the function.

 

[gemma-the-husky]

you cant use vbconstants in the column heading of a query - i think thats why it doesn't like vbmonday

 

[grnzbra]

Thanks for the clarification, gemma. I just tried it and was wondering why it didn't work. However, I then tried it with 2 replacing the vbMonday, and that did work. (Just make sure the 2 doesn't have brackets around it)

 

[sdawson]

Lost here now guys.
Attached is extract of what I'm trying to do
The query byweeknumber does exactly what it says on the tin.
The second query calculates some date but not the wc Monday date.

Any help would be appreciated.

 

[grnzbra]

I apologize.
MonDt = DateAdd("d", -DOW + 1, Date)
should be
MonDt = DateAdd("d", -DOW + 1, Dt)

VBA is seeing Date as Date(). It worked fine for me because I was using during the week to determine what day Monday of that week was.

 

[RuralGuy]

Is this what you wanted? BTW, using reserved words as your names *will* give you unexpected grief some day.
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[grnzbra]

RG,
What's the difference between the straight subtraction and the DateAdd function (besides being neater)? Why bother with DateAdd?

 

[RuralGuy]

They both work. I've gone back and looked at some of my old code and I used both.

 

[sdawson]

Well I'll bite the biggest dog in Bury!
Reseverd words. Doh.

Thanks one and all.

Now, anyone know how Coolmax or Drifit technical clothing is best washed?
?