Re: Week number and day number...

Back to give you more abuse.....


the following should give me this week as number 44 as it used the first
week where Jan 1 enters the year.

=Format(DatePart("ww";Now();1);"00") & Format(DatePart("w";Now();2);"00")

I cannot use the 1st week where there are 4 days as all the calendars here
show this week as the 44th week and both the new code above and the old code
below show the week as the 45th week.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

michael






"Michael" <michael@xxxxxxxxxxxxx> ha scritto nel messaggio
news:u4mofZI4FHA.3880@xxxxxxxxxxxxxxxxxxxxxxx
> Thank you for both answers, I have no idea why I was putting those names
> there except that I saw them on the help page and thought that is what
> should be there... also the ";" solved the other problem. I have never had
> that problem before and have been working here in Europe for 5 years.
>
> thank you
> Michael
>
> "John Spencer" <spencer@xxxxxxxxx> ha scritto nel messaggio
> news:uW9O9SI4FHA.3400@xxxxxxxxxxxxxxxxxxxxxxx
>> Well, you left out the Format function on the second part of the call.
>>
>> =Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")
>>
>> That expression will return 4504
>>
>> If you are in Europe, you are probably using ";" as a separater vice ",".
>> So replace the commas (,) with semi-colons (;).
>>
>> What are FirstWeekOfYear and FirstDayofWeek? For the above to work you
>> should be using a date field or variable and it should be the SAME
>> field/variable in both parts. For example, I used the Now() function
>> which returns the current system date and time.
>>
>> "Michael" <michael@xxxxxxxxxxxxx> wrote in message
>> news:OZ%23P8CI4FHA.1416@xxxxxxxxxxxxxxxxxxxxxxx
>> Thank you John,
>> If I understand what you said in this post, would the following sql give
>> me a number as 4 digits... such as 4401?
>>
>> =Format((DatePart("ww",firstweekofyear,2),"00") &
>> DatePart("w",firstdayofweek,2)"00")
>>
>> I am not sure if firstweekofyear is what should replace "SomeDateField"
>> or not?
>>
>> I am also experiencing a strange problem that may be related to me being
>> in Europe and the comma having some other values. Every time I try to
>> enter this sql, I get the following error.
>> "The expression you entered contains invalid syntax, you omitted an
>> operand or operator, you entered an invalid
>> character or comma, or you entered text without surrounding it in
>> quotation marks:"
>>
>> In another posting Allen Browne suggested that I try working directly in
>> SQL but that does not seem to work. Do you have any suggestions regarding
>> the above code or the error I am receiving?
>>
>> thank you again for all your help
>> Michael
>>
>>
>>
>>
>>
>>
>>
>> "John Spencer" <spencer@xxxxxxxxx> ha scritto nel messaggio
>> news:eaar1KH4FHA.3000@xxxxxxxxxxxxxxxxxxxxxxx
>>> First, DatePart takes a string as the first parameter.
>>>
>>> As to the * 100, you said you wanted a NUMBER not a string of numbers.
>>> To
>>> get that I need to move the week number over two places (multiply by
>>> 100)
>>> and then add the weekday to that. IF you just need a string of number
>>> characters:
>>> Format(DatePart("ww",SomeDateField,2),"00") &
>>> DatePart("w",firstdayofweek,
>>> 2)
>>>
>>> That gets the week number and formats it as two number characters - so
>>> week
>>> 1 to 9 are shown as 01 to 09 then gets the day number and tacks the
>>> number
>>> onto the end, so you end up with 013 for the first week, 3rd day.
>>>
>>> DatePart("ww",Now()) will return the week number based on the which week
>>> has
>>> january 1 in it being week 1
>>> Your other choices for the first week are the first week in the year
>>> that
>>> has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
>>> week with Jan 1 would NOT be the first week since (depending on your
>>> start
>>> day) there would only be 1 or 2 days in that week.
>>>
>>>
>>> "Michael" <michael@xxxxxxxxxxxxx> wrote in message
>>> news:uB7jX2E4FHA.3628@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Thank you John
>>>> I have looked at the help and I am still stuck. I do not understand
>>>> what
>>>> should be in the 1st somedate field or the use of the * 100. The help
>>>> explains the 1st week of the year is the week in which Jan 1 falls is
>>>> the
>>>> default week so I cannot imagine what datefield should be in the first
>>>> part.
>>>>
>>>> =DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)
>>>>
>>>> trying to use the help template ( DatePart («interval»; «date»;
>>>> «firstweekday»; «firstweek») ) for datepart I tried to get just the
>>>> week
>>>> by using this =DatePart([ww];Now();[vbFirstJan1])
>>>>
>>>> also this =DatePart([ww];Now();2;1)
>>>>
>>>> sorry I am confused, can you help me a little other or point me to
>>>> somewhere else to find an answer?
>>>> Michael
>>>>
>>>>
>>>> "John Spencer" <spencer@xxxxxxxxx> ha scritto nel messaggio
>>>> news:uAx5Wh73FHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> Take a look at the DatePart function.
>>>>> DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)
>>>>>
>>>>> I suggest you check out the DatePart function in Visual Basic help as
>>>>> there are additional parameters you can use to specify which day of
>>>>> the
>>>>> week is the first day of the week and how to define the first week of
>>>>> the
>>>>> year. Be aware that if you are using these in a query, you will need
>>>>> to
>>>>> use the values of the vbConstants (vbMonday = 2)
>>>>>
>>>>> "Michael" <michael@xxxxxxxxxxxxx> wrote in message
>>>>> news:eGU%23r%2363FHA.3160@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>I have to have a number that denotes the week number (the number of
>>>>>>weeks
>>>>>> that have past this year) for example this week is the 44th week.
>>>>>> then a
>>>>>> number that denotes the day of the week 01-07 (Monday would be 01) so
>>>>>> today
>>>>>> being wed. would be 4403. Is there a simple way to do this?
>>>>>> thank you
>>>>>> Michael
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
>


.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • RE: Web Part and Access database
    ... I dont know if it is possible to connect to access,but you can download SQL ... Server Error in '/Webparts' Application. ... The connection string specifies a local Sql Server Express instance ... String user, String password, Boolean trusted, String connectionString) ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... Bill Bach wrote: ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)