Want the week number of a given date

[Jan T.]

How can I retrieve the week number of a given date in an SQL-question?

I have tried WEEKNO: Format("ww", [myDate]) and that works fine except
that it assumes Sunday as the first day of the week. What if I use Monday as
the first day of the week?

Any help is very much appreaciated.

Regards
Jan T.

 

[Tom van Stiphout]

On Sun, 15 Feb 2009 19:36:00 +0100, "Jan T."

Read up on the Format command a bit more: you can indicate what you
want to be the first day of the week and the first week of the year.

Be aware that Format depends on the buggy oleauth32.dll and sometimes
will return the wrong weekno: http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP

 

[Jan T.]

Well, in my SQL it looks like this:

SELECT tblDetails.MyDate, Format([MyDate],"ww",[vbMonday],[vbFirstFourDays])
AS WeekNo
FROM tblDetails;

The problem is that when I run the SQL, the Parameter box asks for input
"vbMonday".
Is there a way I can do this without the Parameter box (I may write the
function in a separate
code block in a module, but that will slow down my SQL right?)

Jan

On Sun, 15 Feb 2009 19:36:00 +0100, "Jan T."

Read up on the Format command a bit more: you can indicate what you
want to be the first day of the week and the first week of the year.

Be aware that Format depends on the buggy oleauth32.dll and sometimes
will return the wrong weekno: http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP

How can I retrieve the week number of a given date in an SQL-question?

I have tried WEEKNO: Format("ww", [myDate]) and that works fine except
that it assumes Sunday as the first day of the week. What if I use Monday
as
the first day of the week?

Any help is very much appreaciated.

Regards
Jan T.

 

[John Spencer]

The query cannot recognize any VBA Constants. You need to substitute
the actual values for the VB constants.

vbMonday = 2
vbFirstFourDays = 2

So the statement becomes Format(MyDate,"ww",2,2)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, in my SQL it looks like this:

SELECT tblDetails.MyDate, Format([MyDate],"ww",[vbMonday],[vbFirstFourDays])
AS WeekNo
FROM tblDetails;

The problem is that when I run the SQL, the Parameter box asks for input
"vbMonday".
Is there a way I can do this without the Parameter box (I may write the
function in a separate
code block in a module, but that will slow down my SQL right?)

Jan

On Sun, 15 Feb 2009 19:36:00 +0100, "Jan T."

Read up on the Format command a bit more: you can indicate what you
want to be the first day of the week and the first week of the year.

Be aware that Format depends on the buggy oleauth32.dll and sometimes
will return the wrong weekno: http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP

How can I retrieve the week number of a given date in an SQL-question?

I have tried WEEKNO: Format("ww", [myDate]) and that works fine except
that it assumes Sunday as the first day of the week. What if I use Monday
as
the first day of the week?

Any help is very much appreaciated.

Regards
Jan T.

 

[Tom van Stiphout]

Correct. If you're a purist you may want to write:
Format(MyDate,"ww",GetConstant("vbMonday"),
GetConstant("vbFIrstFourDays"))

and have a function in a standard module like this:
'Returning Variant because the constant could be any data type.
Public Function GetConstant(byval strConstant as String) as Variant
select case strConstant
case "vbMonday"
GetConstant = vbMonday
case "vbFirstFourDays"
GetConstant = vbFIrstFourDays
case else
Msgbox "GetConstant: Unexpected constant requested: " &
strConstant, vbCritical
end select
end function

-Tom.
Microsoft Access MVP

The query cannot recognize any VBA Constants. You need to substitute
the actual values for the VB constants.

vbMonday = 2
vbFirstFourDays = 2

So the statement becomes Format(MyDate,"ww",2,2)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, in my SQL it looks like this:

SELECT tblDetails.MyDate, Format([MyDate],"ww",[vbMonday],[vbFirstFourDays])
AS WeekNo
FROM tblDetails;

The problem is that when I run the SQL, the Parameter box asks for input
"vbMonday".
Is there a way I can do this without the Parameter box (I may write the
function in a separate
code block in a module, but that will slow down my SQL right?)

Jan

On Sun, 15 Feb 2009 19:36:00 +0100, "Jan T."

Read up on the Format command a bit more: you can indicate what you
want to be the first day of the week and the first week of the year.

Be aware that Format depends on the buggy oleauth32.dll and sometimes
will return the wrong weekno: http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP



How can I retrieve the week number of a given date in an SQL-question?

I have tried WEEKNO: Format("ww", [myDate]) and that works fine except
that it assumes Sunday as the first day of the week. What if I use Monday
as
the first day of the week?

Any help is very much appreaciated.

Regards
Jan T.

 

[Jan T.]

Thank you so much, both of you!!

I ended up with the following: Format(MyDate,"ww",2), that is I skiped the
last
argument vbFirstFourDays (Format(MyDate,"ww",2,2)) because that showed me
30.desember and 31.desember and 1.january all as week 1 while skipping the
last argument gave me week 53 for dates in desember and week 1 for 1. of
january.

Regards
Jan T.

The query cannot recognize any VBA Constants. You need to substitute the
actual values for the VB constants.

vbMonday = 2
vbFirstFourDays = 2

So the statement becomes Format(MyDate,"ww",2,2)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, in my SQL it looks like this:

SELECT tblDetails.MyDate,
Format([MyDate],"ww",[vbMonday],[vbFirstFourDays]) AS WeekNo
FROM tblDetails;

The problem is that when I run the SQL, the Parameter box asks for input
"vbMonday".
Is there a way I can do this without the Parameter box (I may write the
function in a separate
code block in a module, but that will slow down my SQL right?)

Jan

On Sun, 15 Feb 2009 19:36:00 +0100, "Jan T."

Read up on the Format command a bit more: you can indicate what you
want to be the first day of the week and the first week of the year.

Be aware that Format depends on the buggy oleauth32.dll and sometimes
will return the wrong weekno: http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP



How can I retrieve the week number of a given date in an SQL-question?

I have tried WEEKNO: Format("ww", [myDate]) and that works fine except
that it assumes Sunday as the first day of the week. What if I use
Monday as
the first day of the week?

Any help is very much appreaciated.

Regards
Jan T.