Wrong Weeknumber

[bjaj]

Hi

I have made this msgbox to display the current Week number, but it
shows the wrong weekno.
Can anyone tell me why ?

msg = "WeekNumber: " & DatePart("WW", Date)
MsgBox msg

It shows weekno = 47 for the date 16 november 2005, but it should be
46.

best regards

Ronald

[Arno R]

Look at the Help

Maybe you need to use:
msg= "WeekNumber: " & DatePart("WW", Date , , vbFirstFullWeek) ?

The return value of DatePart depends on what is to be considered as the first day in a given week.
Also Access needs to know what is considered as the first week.

Arno R

<bj**@vestas.com> schreef in bericht news:11**********************@f14g2000cwb.googlegr oups.com...

Hi

I have made this msgbox to display the current Week number, but it
shows the wrong weekno.
Can anyone tell me why ?

msg = "WeekNumber: " & DatePart("WW", Date)
MsgBox msg

It shows weekno = 47 for the date 16 november 2005, but it should be
46.

best regards

Ronald

[Trevor Best]

bj**@vestas.com wrote:

Hi

I have made this msgbox to display the current Week number, but it
shows the wrong weekno.
Can anyone tell me why ?

msg = "WeekNumber: " & DatePart("WW", Date)
MsgBox msg

It shows weekno = 47 for the date 16 november 2005, but it should be
46.

Check out the 4th parameter to the DatePart function, you should find
what you are looking for for week numbers.

[bjaj]

Hi
Thank both of you. The 4th parameter was the clue.

Thank you wery mutch.

Ronald

[bjaj]

Hi again.

I also got a query with this SQL phrase:

SELECT Q_AfsluttedeRepIntern.InitSag,
Count(Q_AfsluttedeRepIntern.InitSag) AS CountOfInitSag,
Format$(T_FejlRecords.DatoAfsl,'ww\/yyyy') AS [DatoAfsl By Week]
FROM Q_AfsluttedeRepIntern
GROUP BY Q_AfsluttedeRepIntern.InitSag,
Format$(T_FejlRecords.DatoAfsl,'ww\/yyyy');

How shall I do to secure that VB count the weekno. from the first full
week of year ?

best regards
Ronald

[Arno R]

Ronald,
Use the same 'trick'. Look in the Help for Format()

Arno R

<bj**@vestas.com> schreef in bericht news:11**********************@g43g2000cwa.googlegr oups.com...

Hi again.

I also got a query with this SQL phrase:

SELECT Q_AfsluttedeRepIntern.InitSag,
Count(Q_AfsluttedeRepIntern.InitSag) AS CountOfInitSag,
Format$(T_FejlRecords.DatoAfsl,'ww\/yyyy') AS [DatoAfsl By Week]
FROM Q_AfsluttedeRepIntern
GROUP BY Q_AfsluttedeRepIntern.InitSag,
Format$(T_FejlRecords.DatoAfsl,'ww\/yyyy');

How shall I do to secure that VB count the weekno. from the first full
week of year ?

best regards
Ronald

[bjaj]

Hi Arno and thank you!

I know I am a novice i VBA versus Access, so I need more help.
I tryed this:

Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs("Q_3monthBack_Prod")

VareVar = InputBox("Indtast varenr.:", "Indtast")

mySQL = "SELECT Q_AfsluttedeRepIntern.InitSag,
Count(Q_AfsluttedeRepIntern.InitSag)" & _
"AS CountOfInitSag,
Format$(T_FejlRecords.DatoAfsl,'ww yyyy',, vbFirstFullWeek) AS
[DatoAfsl By Week]" & _
"FROM Q_AfsluttedeRepIntern GROUP BY
Q_AfsluttedeRepIntern.InitSag, Format$(T_FejlRecords.DatoAfsl,'ww
yyyy',, vbFirstFullWeek)"
oQuery.SQL = mySQL
Set oQuery = Nothing
Set oDB = Nothing

DoCmd.OpenReport "R_3MonthBack_Prod", acViewPreview

-and got this message:
Syntax error (missing operator) in query expresion
'Format$(T_FejlRecords.DatoAfsl,'ww yyy',,vbFirstFullWeek)'.

What is missing or wrong ?

Please help me !

Best regards

Ronald

[Arno R]

Ronald,
I think with Format$ used in a query, when you use the 4th param you also need to supply the 3rd (StartDay)
Also you need to use the numeric values here:
So I guess Format$(T_FejlRecords.DatoAfsl,'ww yyyy', 1, 3) will work? (I use yyyy, not yyy)
The Constant "1" here means that the week starts at Sunday. (use 2 for Monday)
3 for the last param 'means' the same as vbFirstFullWeek. Again look at the Help for this.

I have a little difficulty here reading your code (my eyes are hazy at the moment ...) but I see some problems here:
-- I don't understand where you use your Inputbox (VareVar)
-- Also you are not using the SQL-string that you are constructing?
Apparently you are changing the SQL of a saved query but you close the query before using it.
==>> oQuery.SQL = mySQL
==>> Set oQuery = Nothing

What exactly are you trying to do, and where are you using this code ??
Is this oQuery.SQL meant to be the recordset for your report??

Btw: As a novice I guess you might be better off using a 'plain' query as the recordset for your report.

Arno R

<bj**@vestas.com> schreef in bericht news:11**********************@f14g2000cwb.googlegr oups.com...

Hi Arno and thank you!

I know I am a novice i VBA versus Access, so I need more help.
I tryed this:

Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs("Q_3monthBack_Prod")

VareVar = InputBox("Indtast varenr.:", "Indtast")

mySQL = "SELECT Q_AfsluttedeRepIntern.InitSag,
Count(Q_AfsluttedeRepIntern.InitSag)" & _
"AS CountOfInitSag,
Format$(T_FejlRecords.DatoAfsl,'ww yyyy',, vbFirstFullWeek) AS
[DatoAfsl By Week]" & _
"FROM Q_AfsluttedeRepIntern GROUP BY
Q_AfsluttedeRepIntern.InitSag, Format$(T_FejlRecords.DatoAfsl,'ww
yyyy',, vbFirstFullWeek)"


oQuery.SQL = mySQL
Set oQuery = Nothing
Set oDB = Nothing

DoCmd.OpenReport "R_3MonthBack_Prod", acViewPreview

-and got this message:
Syntax error (missing operator) in query expresion
'Format$(T_FejlRecords.DatoAfsl,'ww yyy',,vbFirstFullWeek)'.

What is missing or wrong ?

Please help me !

Best regards

Ronald

[bjaj]

Hi Arno

It works with numbers instead of constantnames. Thank you
The code ?
It was taken from some other place where the SQL statement is
different. I shall use the new SQL in a similar way.
And yes, the code is meant to be the recordset for a report.

best regards
Ronald

[jimfortune]

Arno R wrote:

I have a little difficulty here reading your code (my eyes are hazy at the moment ...) but I see some problems here:

The police officer pulled the smart aleck over.

Officer: You're eyes look a little hazy. Have you been drinking?

Smart Aleck: Your eyes look a little glazed. Have you been eating
doughnuts?

James A. Fortune

[Wayne Gillespie]

On 16 Nov 2005 00:07:06 -0800, bj**@vestas.com wrote:

Hi

I have made this msgbox to display the current Week number, but it
shows the wrong weekno.
Can anyone tell me why ?

msg = "WeekNumber: " & DatePart("WW", Date)
MsgBox msg

It shows weekno = 47 for the date 16 november 2005, but it should be
46.

best regards

Ronald

Beware of this gotcha -

BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year
http://support.microsoft.com/default...b;en-us;200299