Wrong week number - 1966

Hi,

(Access 2007)

I have a query with an expression in a field like:

WEEK: Format$([Table1].[Date1];'ww')

Above expression is always giving next week's week number if the day is 
Sunday. What can it be the problem. Any idea?

Best Regards. 


0
Art
3/4/2008 8:54:58 PM
access 16762 articles. 3 followers. Follow

2 Replies
1342 Views

Similar Articles

[PageSpeed] 20

Art,

No, this is not wrong. The date formats use Sunday as the first day of 
the week by default.  If you want it to use another day, you need to 
specify.  Also, drop the $.  To start the week on Monday, try it like this:

WEEK: Format([Table1].[Date1];'ww';2)

-- 
Steve Schapel, Microsoft Access MVP

Art Vandaley wrote:
> Hi,
> 
> (Access 2007)
> 
> I have a query with an expression in a field like:
> 
> WEEK: Format$([Table1].[Date1];'ww')
> 
> Above expression is always giving next week's week number if the day is 
> Sunday. What can it be the problem. Any idea?
> 
> Best Regards. 
> 
> 
0
Steve
3/4/2008 9:21:33 PM
On Mar 4, 4:54=A0pm, "Art Vandaley" <kesta...@hotmail.com> wrote:
> Hi,
>
> (Access 2007)
>
> I have a query with an expression in a field like:
>
> WEEK: Format$([Table1].[Date1];'ww')
>
> Above expression is always giving next week's week number if the day is
> Sunday. What can it be the problem. Any idea?
>
> Best Regards.

The default for dates is to use Sunday as the first day of the week.
So if its sunday, it belongs on the following week. You can specify
you First day though.

WEEK: Format$([Table1].[Date1];'ww';vbMonday) where vbMonday =3D 2. This
will turn: Sun, Mon, Tue, Wed, Thu, Fri, Sat week into a Mon, Tue,
Wed, Thu, Fri, Sat, Sun week. Hope this works.

You can also use the following function:

WEEK: DatePart('ww',[Table1].[Date1],vbMonday)

PS.: This is for Access 2003 but it should be the same on 2007.
-GL
0
Guillermo_Lopez
3/4/2008 9:26:46 PM
Reply:

Similar Artilces:

Delete numbers
I need to import a price list from Excel to SAP. The problem is that need to import a price with only two digits after the period lik 63.45. The problem is I was sent a price list with many digits lik 63.4578932 or 12.3268246987. I would like to permanently delete the digits forward and just keep the two digits after the period as i 63.45 or 12.32. I know about the increase and decrease decimal. But need to permanently delete the digits. Can anyone help? Thank you Roge -- Rogerm ----------------------------------------------------------------------- Rogermb's Profile: http://www.exc...

Numbers automatically change to a symbol?
why do fractions suddenly change and appear as a symbol in word Which fractions? If it's 1/4, 1/2, and 3/4, then the likelihood is that (a) you have "Fractions (1/2) with fraction character (½)" enabled on the AutoFormat As You Type tab of AutoCorrect Options, and (b) the font you're using doesn't contain Unicode characters 00BC, 00BD, and 00BE (ASCII 188, 189, 190). -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Bruce L" <Bruce L@discussions.microsoft.com> wrote in message...

preventing Excel from changing number formats
When displaying charts, I make a point to select the particular display format (dates as MM/DD/YYYY, numbers with comma separators, etc.). However when I copy the charts from one spreadsheet to another, or send the charts via email, the formats sometimes change back to the way it was represented originally (dates as numbers, no comma separator, etc.) What's weird is that this doesn't happen consistently (only some charts get affect). Is there any way to prevent this, reverting of formats, from happening? It's becoming really annoying. Thanks for your help. ...

event id 1233
We are getting event ID 1233 alerts for a handful of users. The message/alert states that the user has exceeded a configured maximum of open messages. The message is only displayed in the Ex2k application log and not in the outlook client interface. Also, users don't report having more than a few messages open at the time of the alert. I can't find any client rules/constraints. Where is this limit set? Are these users using some sort of desktop synchronizing problem, such as to a Blackberry or PDA? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick ...

Maximum number of LUNs for Windows Server 2008 (non-R2)
Would anyone know what the maximum number of LUNs allowed by Windows Server 2008 32/64bit are? I recall reading somewhere that Server 2003 SP1 had a maximum of 256, but I cannot seem to find the 2008 information anywhere. Hi sBox, I personally haven't heard of an LUN limitation in Windows 2008 so far yet but what exactly do you want to achieve here ? Your first limitation will be the drive letters but this can be solved with mountpoints. Just for my interesting, what is your setup ? You should check "storage limits" on technet, if that does helps you. Regard...

strange column to the left of my row numbers column
Excel 2000 I am using an Excel spreadsheet that was exported from another program. There is some kind of column to the left of my row numbers column with a little 1 button and a little 2 button on top. Then there are dots at each row down. Apparently the purpose is to jump to the last row where I have data, because that is what happens when I click on 1 or 2. I can't see where it is applied in View or Customize. I've looked through Help and can't find any reference to what this feature is. Nor can I right click and get any information on what it does or how to delete it. I...

How to convert text to number format
I copy a table of numbers from web to Excel. They appear as text and I can't do algebric command on them (For example their "sum" become zero) I know it is possible to change number format to text format but is there any command to convert numbers to texts so I can sum up them? Changing the format from: Format/Cells Number tab and category box doesn't help. Any comment is appreciated TIA Rasoul Rasoul Copy an empty cell. Select your range of data. Paste Special>Add>OK>Esc. Gord Dibben Excel MVP On Tue, 13 Jan 2004 00:00:54 +0330, "Rasoul Khoshravan Azar&...

how do i get negative numbers in brackets in Excel
i find i cannot enter negative numbers in accounts in brackets but this is the accounting convention is it hidden in excel or do i need a plug in Hi Maverick, Enter numbers using the minus symbol, but format your cells: Format => Cells => Number [tab] and in the Type box, enter "#,##0.00_ ;[Red](#,##0.00) " (without the quotation marks). Ewan. "maverick accountant" wrote: > i find i cannot enter negative numbers in accounts in brackets > but this is the accounting convention > is it hidden in excel > or do i need a plug in ...

Listing just a single actual months events from a table over a number of years.
In Access I have a table of events with one column having the date dd/mm/yyyy. I want to, for example, list all the events that took place in the month of February in all years. so it would extract 12/02/1999, 25/02/2003 and so on. And then later,all the November events, etc. Thanks for your help. Jim. Hi you may post this to the Access NG. But also Acces should have a month function IIRC and you could create a user defined field in your query -- Regards Frank Kabel Frankfurt, Germany jimsunz wrote: > In Access I have a table of events with one column having the date > dd/mm/yyy...

Sum of all even and odd numbers between two columns
I have column "A," and "B," with random numbers listed. I need a formula to add just the even numbers in column "A," and all the odd numbers in column "B." I.E.(Range A1:A10 & B1:B10) Help! Oz One way Put in C1: =SUMPRODUCT(--(MOD(A1:A10,2)=0),A1:A10) (add even numbers in col A) Put in D1: =SUMPRODUCT(--(MOD(B1:B10,2)=1),B1:B10) (add odd numbers in col B) -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Oz" <Oz@discussions.microsoft.com> wrote in message news:13BA...

Need item master to include multiple manufacturer part numbers.
GP Manufacturing Suite needs to have ability to support multiple manufacture and manufacturer part numbers to facilitiate second sources. It does not seem there is any such capability to do so now. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.c...

axis numbers
Somehow my axis numbers jumped to the right. Why, and how to get them back on the left. UWcincy wrote on Fri, 30 Jun 2006 07:48:02 -0700: U> Somehow my axis numbers jumped to the right. Why, and how to U> get them back on the left. I guess you mean the y-axis and you have probably managed to check "Value (Y) axis crosses at maximum value." Right click the x-scale numbers to fix it. James Silverton. Or you plotted the X values in reverse order, which keeps the Y axis at the minimum of the X axis. Now you need to check thee Axis Crosses at Maximum box in the For...

convert text to number for a range of data in a column
I have a set of data that are numbers stored as text. CLicking on the little green tab enables me to convert the text in the cell into a number. All The data in the column starting from a certain row and ending at a certain row are numbers stored as text. Also, it's a very long long long list. Is there a shortcut to converting all the data to numbers without having to click the green tab for each cell or having to highlight all the cells in the column (only to zip past the last row) and go through cell properties? Thanks In an un-used cell enter 1. Copy the cell and then paste/spe...

How do I put a reference number in the margin in Word
I'm writing a report and need to put a note in the margin of some paragraphs with a reference number such as D1 in it. I could add it as a comment, but that is not how it should look, unless there is a way of just having the comment in the margin without the bubble and the line to the text. Any suggestions please? Dave See http://sbarnhill.mvps.org/WordFAQs/MarginalText.htm. -- Stefan Blom Microsoft Word MVP "Bally67" <Bally67@discussions.microsoft.com> wrote in message news:E718AA4A-3303-48BE-9F65-FE6E5A0F5E45@microsoft.com... > I'm wr...

Number of incoming messages is incorrect
At times I notice that the number of incoming mail I receive is greater than I actually get. I have blocked a lot of junk mail messages/spam but are these being considered as incoming mail anyway? -- Bugsy Any incoming message is considered new regardless of what folder it ends up in. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Its My Bug" <ItsMyBug@discussions.microsoft.com> wrote in message news:4AD6E858-98F4-44A7-B572-362C4E165B71@microsoft.com... > At times I notice that the number of incoming mail I receive is gre...

Purchase Order automatic number sequence
Somehow one of my employees has inadvertantly change the sequence of the automated purchase order numbers. The sequence matches too closely with another locations number sequence. How can I alter or set a new number sequence to stop this. -- blueindian This is a multi-part message in MIME format. ------=_NextPart_000_0198_01CA595B.A8CB1D20 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Blueindian, You will have to change it with a SQL script. Make a note of the PO number you wish to change. Make a backup! Instructions for a Singl...

how do i build commas on numbers in india system
1. How do I put comas in numbers under Indian sytem? 2. Is it possible to view the header and footer on the excel spread sheet screen? 3. How to get rank in a given table "Vijay" <Vijay@discussions.microsoft.com> wrote in message news:EB7E2F4B-F034-4BEE-8D59-EE1706FCC9AD@microsoft.com... > 1. How do I put comas in numbers under Indian sytem? Use acustom format of [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 will format say 1234567890 as 123,45,67,890.00 > 2. Is it possible to view the header and footer on the excel spread sheet > sc...

Transfering a Date into a day of the week.
I have a row with dates (01-Apr-2005 to 31-Oct_2004) I am trying to figure out the day of the week in the row next to the date. I would appreciated any help you can provide. Donne ...

what is wrong with this formula
=SUMPRODUCT((A1:A21=D1)*(B1:B21),(F1:F7))/SUMPRODUCT(--(A1:A21=D1),--(B1:B21)) results in #VALUE! #WHY? "Norbert" <n.jaeger@gmx.net> wrote: > =SUMPRODUCT((A1:A21=D1)*(B1:B21),(F1:F7)) > /SUMPRODUCT(--(A1:A21=D1),--(B1:B21)) > results in #VALUE! #WHY? One reason: because the ranges A1:A21, B1:B21 and F1:F7 are not all the same size. Another possible reason: If "F1:F7" is a typo and you mean F1:F21, because some cells in B1:B21 contain text, not numbers or boolean values (true, false). If B1:B21 contains boolean values (true, fa...

View page number on 2007 Status Bar
I am running Windows 7 and recently installed Office 2007 on it (previously used Office 2003 on XP machine). When Word loads, it does NOT show my the page number on the Stagus bar. The only info I see in the bottom left is Word count. If I click "Full Screen Reading" view (bottom right on the Status Bar) and then click Escape to exit that view, I do see "Page 1 of 1" and the position in the bottom left (along with the Word count). But, if I exit the program and re-enter it, I only find Word count in the bottom left. How can I get Page number to show as ...

sheet printing in wrong color
When I print a sheet that I want to print in black and white and some numbers in red, the sheet prints in blue and some numbers in red. If I change the sheet to print in b/w, the red numbers also print in black. I have tried selecting the entire sheet and choosing automatic font color and no fill. Does anyone know how to fix this problem? The grid alos prints in this blue shade... Thanks ...

number column
what is the name for the column on the left with the numbers? i need to know for the sake of equations i am trying to get to work online Well, why do you require the name of this column. You just need to know the 'name' of the cell which can be referred to by its column number and its row number. Mangesh "thelfj" <thelfj@discussions.microsoft.com> wrote in message news:34E87FED-0427-4325-8DA4-B2AD384B76F4@microsoft.com... > what is the name for the column on the left with the numbers? i need to know > for the sake of equations i am trying to get to work onli...

Enter Serial numbers after the fact
In the Field Service Contract Administration module, Is there a way to add or change the serial number to an item after the contract has been invoiced? Occasionally during the contract entry process the serial number doesn't get entered and it's not caught before the contract is invoiced. Or an item is returned for exchange and we need a way to put the new serial number on the contract. Thanks. DavidF. ...

drop the thousands place in a currency number
I have a number 63.262 that I want to multiply another number by. Before it multiplies I want to round off to the nearest hundredth? 63.26 I am starting with a formula from other cells and not a specific numbe although the 63.262 is one of the variations. Thank Yo -- Message posted from http://www.ExcelForum.com You can use the round function during the muliplication. =ROUND(67.3*0.94,2) o =ROUND(A1*B1,2) for cell referencin This will drop the thousand place. If you want to round first, then multiply, use: =round(a1,2)*b1 -- Regards, Fred Please reply to newsgroup, not e-mail "...

Portfolio Manager displays cost with the wrong totals
I have Money 2006 and I set up my investments in portfolio manager. The cost totals for my investments do not add correctly. I don't have a massive portfolio so we are only talking about 2-3 mutual funds with a broker. Initial purchases and subsequent purchases were fine. Everything totaled and cross footed. But, that all changed when I partially sold them to pay my son's college tuition. Ex: Mutual fund Cost Market Value Fund ABC $500 $625 Fund 123 $1000 $1250 Total $1750 ...