Wrong Weeknumber

[bjaj@vestas.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

[Arno R]

Re: Wrong Weeknumber

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

<bjaj@vestas.co m> schreef in bericht news:1132128426 .049921.213560@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> 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
>[/color]

[Trevor Best]

Re: Wrong Weeknumber

bjaj@vestas.com wrote:[color=blue]
> 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.[/color]

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

[bjaj@vestas.com]

Re: Wrong Weeknumber

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

Thank you wery mutch.

Ronald

[bjaj@vestas.com]

Re: Wrong Weeknumber

Hi again.

I also got a query with this SQL phrase:

SELECT Q_AfsluttedeRep Intern.InitSag,
Count(Q_Afslutt edeRepIntern.In itSag) AS CountOfInitSag,
Format$(T_FejlR ecords.DatoAfsl ,'ww\/yyyy') AS [DatoAfsl By Week]
FROM Q_AfsluttedeRep Intern
GROUP BY Q_AfsluttedeRep Intern.InitSag,
Format$(T_FejlR ecords.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]

Re: Wrong Weeknumber

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

Arno R

<bjaj@vestas.co m> schreef in bericht news:1132136291 .237340.186800@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> Hi again.
>
> I also got a query with this SQL phrase:
>
> SELECT Q_AfsluttedeRep Intern.InitSag,
> Count(Q_Afslutt edeRepIntern.In itSag) AS CountOfInitSag,
> Format$(T_FejlR ecords.DatoAfsl ,'ww\/yyyy') AS [DatoAfsl By Week]
> FROM Q_AfsluttedeRep Intern
> GROUP BY Q_AfsluttedeRep Intern.InitSag,
> Format$(T_FejlR ecords.DatoAfsl ,'ww\/yyyy');
>
> How shall I do to secure that VB count the weekno. from the first full
> week of year ?
>
> best regards
> Ronald
>[/color]

[bjaj@vestas.com]

Re: Wrong Weeknumber

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_Pr od")

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

mySQL = "SELECT Q_AfsluttedeRep Intern.InitSag,
Count(Q_Afslutt edeRepIntern.In itSag)" & _
"AS CountOfInitSag,
Format$(T_FejlR ecords.DatoAfsl ,'ww yyyy',, vbFirstFullWeek ) AS
[DatoAfsl By Week]" & _
"FROM Q_AfsluttedeRep Intern GROUP BY
Q_AfsluttedeRep Intern.InitSag, Format$(T_FejlR ecords.DatoAfsl ,'ww
yyyy',, vbFirstFullWeek )"


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

DoCmd.OpenRepor t "R_3MonthBack_P rod", acViewPreview

-and got this message:
Syntax error (missing operator) in query expresion
'Format$(T_Fejl Records.DatoAfs l,'ww yyy',,vbFirstFu llWeek)'.

What is missing or wrong ?

Please help me !

Best regards

Ronald

[Arno R]

Re: Wrong Weeknumber

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_FejlR ecords.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

<bjaj@vestas.co m> schreef in bericht news:1132175438 .440819.292070@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> 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_Pr od")
>
> VareVar = InputBox("Indta st varenr.:", "Indtast")
>
> mySQL = "SELECT Q_AfsluttedeRep Intern.InitSag,
> Count(Q_Afslutt edeRepIntern.In itSag)" & _
> "AS CountOfInitSag,
> Format$(T_FejlR ecords.DatoAfsl ,'ww yyyy',, vbFirstFullWeek ) AS
> [DatoAfsl By Week]" & _
> "FROM Q_AfsluttedeRep Intern GROUP BY
> Q_AfsluttedeRep Intern.InitSag, Format$(T_FejlR ecords.DatoAfsl ,'ww
> yyyy',, vbFirstFullWeek )"
>
>
> oQuery.SQL = mySQL
> Set oQuery = Nothing
> Set oDB = Nothing
>
> DoCmd.OpenRepor t "R_3MonthBack_P rod", acViewPreview
>
> -and got this message:
> Syntax error (missing operator) in query expresion
> 'Format$(T_Fejl Records.DatoAfs l,'ww yyy',,vbFirstFu llWeek)'.
>
> What is missing or wrong ?
>
> Please help me !
>
> Best regards
>
> Ronald
>[/color]

[bjaj@vestas.com]

Re: Wrong Weeknumber

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@compumarc.com]

Re: Wrong Weeknumber

Arno R wrote:[color=blue]
> I have a little difficulty here reading your code (my eyes are hazy at the moment ...) but I see some problems here:[/color]

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]

Re: Wrong Weeknumber

On 16 Nov 2005 00:07:06 -0800, bjaj@vestas.com wrote:
[color=blue]
>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[/color]

Beware of this gotcha -

BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

http://support.microsoft.com/default.aspx?scid=kb;en-us;200299