WEEKNUM

[kmacd6951]

In Excel there is WEEKNUM function to tell you the Week # for a given date. You can also specify whether you want weeks to begin on Sunday or Monday by using a "1" or "2" in your expression.

In Access, I have gotten as far as using FORMAT(fieldname, "ww")
This gives me the week number, but it is doing it based on the week starting on a Sunday and I need it to start the weeks on Monday's.

Any suggestions?

Thanks

 

[CT Witter]

your almost there! Try

FORMAT(fieldname, "ww",vbMonday)

CT

 

[kmacd6951]

Not quite.....

Here is my Expression in the Expression Builder:

Created Week: Format([SWB_SW_CASE]![SWDATECREATED],"ww",[vbMonday])

It put the [ ] brackets around vbMonday when I saved the Query.

However, when I go to run this query, it is now prompting me with a pop up box that says vbMonday
Do you know why? Or how to prevent that?
When I take out the vbMonday, it runs fine, except that the weeks start on Sunday

Thanks

 

[mdmilner]

Not sure why it did that but if you go into SQL view and remove the brackets around vbMonday, you should be good.

 

[CT Witter]

Try:
Created Week: Format([SWB_SW_CASE]![SWDATECREATED],"ww",2)

HTH,
CT

 

[kmacd6951]

I tried removing the [ ] through SQL view. It removed them, but I am still getting the pop up for vbMonday
If I leave it blank, I don't get any results back for my query.
Odd... ?!??!

Any other ideas on this one?

Thanks

 

[kmacd6951]

I Got it!!!

Needed to use a "2" which represents vbMonday

OHhhh! And ... I just noticed that CT Witter suggested that! Thank you

 

[kmacd6951]

Almost got it that is...

How do I change it so that the value doesn't export to Excel as a text field, but rather a number?
I tried to right click and set the properties to make it a General Number, but it still exported to Excel as Text.

 

[bat17]

You could try using the DatePart(interval, date[,firstdayofweek[, firstweekofyear]]) Function which is, for a change, better than its Excel equivelent!

Peter

 

[kmacd6951]

Thank you! DatePart did it:

Created Week: DatePart("ww",SWB_SW_CASE!SWDATECREATED,2)