Hi, I have a problem with my query which shows week numbers on a column. Week numbers derived from date field of table. Column has below string: WEEK: Format$([Table1].[Date1];'ww') Above equation gives next week number for Sundays. On my computer date system is normally adjusted to start the new week number on Mondays. At the other hand I have a string calculating week number for my form like: WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays) Above formula gives correct result. For example: 24th. February 2008 ==> My form gives 8 and my query gives 9 for week number. Does anybody have an idea? Best Regards.

0 |

2/29/2008 9:56:47 AM

One method (there are others) week: IIf(Format([Table1]![Date1],"w")=1,Format([Table1]![Date1],"ww"),Format([Table1]![Date1],"ww")+1) -- Wayne Manchester, England. "Art Vandaley" wrote: > Hi, > > I have a problem with my query which shows week numbers on a column. Week > numbers derived from date field of table. Column has below string: > > WEEK: Format$([Table1].[Date1];'ww') > > Above equation gives next week number for Sundays. On my computer date > system is normally adjusted to start the new week number on Mondays. > > At the other hand I have a string calculating week number for my form like: > > WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays) > > Above formula gives correct result. For example: > > 24th. February 2008 ==> My form gives 8 and my query gives 9 for week > number. > > Does anybody have an idea? > > Best Regards. > > >

0 |

2/29/2008 11:50:00 AM

Art Vandaley wrote: > Hi, > > I have a problem with my query which shows week numbers on a column. Week > numbers derived from date field of table. Column has below string: > > WEEK: Format$([Table1].[Date1];'ww') > > Above equation gives next week number for Sundays. On my computer date > system is normally adjusted to start the new week number on Mondays. > > At the other hand I have a string calculating week number for my form like: > > WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays) > > Above formula gives correct result. For example: > > 24th. February 2008 ==> My form gives 8 and my query gives 9 for week > number. > > Does anybody have an idea? Art, You've already answered your own question! The Expression Service in Access' queries do not know about the named constants you are using in your form code. In the query, try replacing the constants with their integral values (in QBE): WEEK: Format([Table1].[Date1], "ww", 2, 2) or vbMonday: 2 vbFirstFourDays: 2 WEEK: Format([Table1].[Date1], "ww", vbMonday, vbFirstFourDays) or in SQL: SELECT 2 AS vbMonday, 2 AS vbFirstFourDays, Format([Table1].[Date1], "ww", vbMonday, vbFirstFourDays) AS WEEK, ... James A. Fortune MPAPoster@FortuneJames.com

0 |

2/29/2008 8:07:32 PM

how to change a column of numbers to a date like 2411 to 24/11/05 3011 to 30/11/05 The formula is - =value(left(a2,2)&"/"&right(a2,2)&"/2005") in a cell that has been formatted to dd/mm/yyyy Regards. Bill Ridgeway Computer Solutions "abe" <abe@discussions.microsoft.com> wrote in message news:43B9BF48-262E-42BA-AE63-F8AE44834BC4@microsoft.com... > how to change a column of numbers to a date like > 2411 to 24/11/05 > 3011 to 30/11/05 Hiya Abe, Format Style Modify Date Then select the style you want Hope that helps :) -- ...

How to get the digit number of a LONGLONG data? for example:LONGLONG aaa=123; then the digit number is 3; LONGLONG aaa=5678; then the digit number is 4; Another way is: int digits = 0; LONGLONG number = aaa; while (number > 0) { number /= 10; digits++; } Geo >-----Original Message----- >How to get the digit number of a LONGLONG data? >for example:LONGLONG aaa=123; then the digit number is 3; > LONGLONG aaa=5678; then the digit number is 4; > > >. > As long as the number isn't negative. "Geo" <ckarakiro@hotm...

When opening a .slk file in Excel 2002 you can only see the first 32,767 records. I have a file that is larger than this , and therefore I cannot see all of the data. I know that you can extend the maximum number of records when opening a text file - I just need to know how to do it. Can anybody help me please? Opening a text file and a SYLK file are two different operations "under the hood". If 32,767 is the limit, there's probably not much you can do about it. Do you have control over the workbook from which the SLK file was produced? If so, can you split it into two ...

I want to find out the HDD Serial Number in my C++ Code. There seems to be a way using the WMI. But I donot (cannot) use it. I want to know if there is some C++, C or assembly code that can help me do this. Any help/suggestions would be very helpful and highly appreciated. Anwar. You can do this with GetVolumeInformation() Fariborz "Anwar Khan" wrote: > I want to find out the HDD Serial Number in my C++ Code. > There seems to be a way using the WMI. But I donot (cannot) use it. > > I want to know if there is some C++, C or assembly code that can help me do &g...

It's probably here somewhere - just can't find it. Need to automatically add incremental number as various users ope common Excel Template (stored in common template directory) so as eac person opens a new file from the template the next number i displayed. Have tried adapting standard Invoice that comes with Excel but reall is too complicated - only need to change the one cell each time. Using Excel 2000 with win98SE If someone can point me in the right direction... thanks.. ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and po...

I'm trying to set a cell format to either $ or % determined by the value input by the selection of one of two lists dependent on a $ or % selection criteria in another cell. If the value input is within the range of -1 to 1, I need it to display in a percentage format. If it is less than -1 or greater than 1, it needs to display as a currency format (or comma w/ 2 decimals). I prefer using data validation lists on this sheet instead of other controls due to their larger size. The version is Excel 2000. Any assistance is appreciated. You could use a helper cell to show the f...

When entering currency data into a form Access rounds the number up or down to the nearest dollar. The fields for the form and table are formatted for currency, 2 decimal places, field size is long integer. I am not using any queries. How do I prevent rounding numbers to nearest dollar? MS Access 2003 Thanks. Hi, Ron. > When entering currency data into a form Access rounds the number up or > down > to the nearest dollar. The fields for the form and table are formatted for > currency, 2 decimal places, field size is long integer. Since Long data types are "whole num...

Hello. What do I do with my page numbers when I have 2 section breaks in 1 page. -(Page) at (SectionPages)- Right now it says 1 to 1 beacause of the section breaks. I would like it to be 1 to 5 ex. My problem is that I have a title which is framed in 2 section breaks. Im doing this because I write in columns and i dont want the title to be in columns-format. The problem is so, that it counts the title of an independent section and confuse the page number. How do I get it to count for a single section? Please Help Instead of using section breaks around the title, I would pro...

Hi there I have several section and the footer shows page # of #. In each section, I want it to start at 1 of number of pages in the section. I figured out how to change the first number so it is correct-- but I can not figure out how to change the total number of pages in the document to the total number of pages in the section. How do you change it so is show the total number of pages in the section? -- Thank-you! Ruth Use the SectionPages field instead of NumPages. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mv...

I have probably an obvious question, but nonetheless, here it goes....One of our users has an excel worksheet where it shows up as A, B, C,, etc. along the top and 1, 2, 3, etc. along the left side. When she sends it to someone else in our office, it is coming up as 1, 2, 3 along the top and sides. Is this a setting that the second user has? I haven't been able to find a solution online for it. Help! Thanks, Kerri Tools|Options|General Tab Check R1C1 reference style to see numbered columns. Uncheck it to see lettered columns. Kerri Schultz wrote: > > I have probably an o...

I did not receive a confirmation e-mail with my product key number when I downloaded the 60-day free trial of Microsoft Office 2007. How do I locate it? You need that email. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Pat77" <Pat77@discussions.microsoft.com> wrote in message news:E7222010-D27B-4C82-BA5F-4994154BA0E3@microsoft.com... >I did not receive a confirmation e-mail with my product key number when I > downloaded the 60-day free trial of Microsoft Office 2007. How do I > l...

Hi dude, I am new to excel .. here is my requirements. Code: -------------------- : 449 78 d 449 69 m d 458 260 m 469 28 m 472 39 m 479 51 d 484 59 m d -------------------- i need to count number of "d" present in the 4 th column and number of m present in the 3 column.. its a very big table and i need to calculate programically. how to do it? thanks in advance !! -- jayender.vs ------------------------------------------------------------------------ jayender.vs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28640 View ...

Hi All, I am facing a problem with automatic generate PO Number, the application is working on this senario: SELECT ISNULL(MAX(CAST(RIGHT(PONumber, 7) AS INT)), 0) + 1 AS NextPONumber FROM PurchaseOrder WHERE ISNUMERIC(PONumber) <> 0 my max is '9999999', so it is generate a new one with PONumber = '10000000' then every time it is cannot generate a next one cause of '0000000' can anyone help me on this ? ...

I need to seperate the numbers and words in the same cell. Not all tex and/or numbers are the same or are the same amount of characters. Ex. 22454 IN is all in the same cell I want: 22454 to be in one cell and IN in another cell to the right Thank -- Message posted from http://www.ExcelForum.com I assume that the original cell is text. If it is, then you can look for the position of a blank, then separate the two based on the blank location. If cell A1 has "22454 IN", In cell A2: = Search(" ". A1) In cell A3: = Left(A1,A2-1) In cell A4: = Right(A1,Len(A1) -...

Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

I have the last column of data. I want to use a range to say copy or define something. So how do you convert the number LastCol to a letter to display in a Range: LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column A web search for "convert column number to letter" turned up several hits, one of which was: http://www.mrexcel.com/forum/showthread.php?t=102298 look at the last 3 posts in that discussion. Two ways to get the job done. Personally, since you've gone the route you have at this point, I'd probably go with the solution in the last ...

Can someone help me? I made a work distribution spreadsheet and I can not get it to divide evenly. What I am trying to do is divide the total number of claims by the number of employees and distribute the result to each employee. There are conditions also. For example one employee gets the max number of 50; Here is my formula; =IF(E18=0,0,IF(I19>250,50,(I19/(E8+E9+E10+E16)*E18))) column E =# of hours working for each employee, I19 = total number of claims. If the total number of claims is 1500 and there are 5 people working it returns 50 to the E18 employee and 363 to the rest, but t...

I have some data up to row 2200. Unfortunately, the data was capture with a glitch, to where all even numbered rows (2, 4, 6,.....2200 contain bogus data. I want to delete all even numbered rows in the spreadsheet, and hav the 1100 odd numbered rows shift up, so I have only 1100 rows of th real data on the spreadsheet -- Yel26 ----------------------------------------------------------------------- Yel268's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1570 View this thread: http://www.excelforum.com/showthread.php?threadid=27234 Hi one way: - in a helper c...

How do you remove a quantity from inventory for an item that uses lot numbers. Currently the Stock Status reports shows a quantity of 167 but it should say zero. I tried doing an inventory adjustment and I had to create a new lot number. It posted through ok, but when I check the Stock Status report, the item is still there showing 167. I'm stumped. bump "Shelleyb57" wrote: > How do you remove a quantity from inventory for an item that uses lot > numbers. Currently the Stock Status reports shows a quantity of 167 but it > should say zero. I tried doing an invent...

Need some help with this one please; Row A4 thru J4 A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 12:00 15:00 16:00 I need to return to cell K4 the last number in the above row. I do not know what formula to use. Any help would be greatly appreciated. There are several ways to achieve that, - here are two: http://img9.imageshack.us/img9/9398/nonamegi.png Micky "v1rt8" wrote: > Need some help with this one please; > Row A4 thru J4 > A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 > 12:00 15:00 16:00 > ...

I have a column of data with numbers relating to specific industries. Each row corresponds to an individual and their affiliated industries. Some cells contain 1 number and some contain several (separated by commas). I would like to break this column apart into each individual industry for separate columns. The corresponding person would have an x in the column that refers to the industry they relate to. I have a separate sheet for what each code (number) means. HELP! Have you checked out Data -> Text to Columns... To split out your numbers? -- HTH... Jim Thomlinson ...

how can i do the the work to havee to do The formula to sum all the numbers is =SUM(A1:IV65536) -- David Biddulph "cristian" <cristian@discussions.microsoft.com> wrote in message news:282443DB-841A-44FF-A239-6E3AD25BD5A6@microsoft.com... > how can i do the the work to havee to do One way to get the work done is provide more detail in the body of your message. Your subject line reads only............ "Write a sum formula to determine the total of all the numbers bel" The formula to sum all numbers in column A below 46 is =SUMIF(A1:A1...

This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C4E2BF.8F7AB0F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there any workaround for setting up a bill payment frequency of 8 = weeks? I have a subscription that I am billed for every 8 weeks. = Selecting the option of "Every Other Month" does not work for me, as = that is not truely 8 weeks. Thanks, The Merg --=20 Today's problems don't worry me, I haven't solved yesterday's yet. ------=_NextPart_000_0006_01C4E2BF.8F7AB0F0 Conten...

I am recieve data from a scale in text box. It has number and other carater. How I could take only number in text box. I am work with 2008. thanks for your help "Roberto Londono" <RobertoLondono@discussions.microsoft.com> wrote in message news:546A43AC-C590-42C0-BB4C-DA3C6D71FAF0@microsoft.com... >I am recieve data from a scale in text box. It has number and other >carater. > How I could take only number in text box. I am work with 2008. > > thanks for your help It depends on how you receive your data, but I did this for a similar situation. I...

I am using Mmoney for ppc2005 on Windows Mobile 2003. I made full back up of all data to escape problems when upgrading from MMoney 2002 to MMoney 2005. Now I can not open the old MMoney 2002 file, because versions turned to be incompatible. When I thought I could lode the database from My Pocket PC, after I made full restore in it MMoney opens only the last 4 weeks with MMoney data instead of 2 years. Where is my backed up data for 2 years that I backed up? Did you set ActiveSync to synchronize all data rather than 4 weeks for Money for the pocket pc? If you didn't, then you'll...