Forcing a zero
Is there anyway to make a field that has a zero at the front of it? 28377310874 028377310874 Meaning, I want the second of the above to show, not the first.
|
0 |
|
John
12/17/2009 3:14:34 AM
excel
39879 articles. 
2 followers.
18 Replies
703 Views
Hi John, Format the cell with the number format you require. In this case: 000000000000 -- Cheers macropod [Microsoft MVP - Word] "John Persico" <reply@newsgroup.com> wrote in message news:e2rOQcsfKHA.1648@TK2MSFTNGP05.phx.gbl... > Is there anyway to make a field that has a zero at the front of it? > > 28377310874 > 028377310874 > > Meaning, I want the second of the above to show, not the first. >
|
|
0 |
|
macropod
12/17/2009 4:47:04 AM
On Dec 17, 9:47=A0am, "macropod" <macro...@invalid.invalid> wrote: > Hi John, > > Format the cell with the number format you require. In this case: > 000000000000 > > -- > Cheers > macropod > [Microsoft MVP - Word] > > > > "John Persico" <re...@newsgroup.com> wrote in messagenews:e2rOQcsfKHA.164= 8@TK2MSFTNGP05.phx.gbl... > > Is there anyway to make a field that has a zero at the front of it? > > > 28377310874 > > 028377310874 > > > Meaning, I want the second of the above to show, not the first.- Hide q= uoted text - > > - Show quoted text - right click, select format cells, select custom and enter 12 zeros in Type:.
|
|
0 |
|
Sane
12/17/2009 5:00:11 AM
John Persico wrote: > Is there anyway to make a field that has a zero at the front of it? > > 28377310874 > 028377310874 > > Meaning, I want the second of the above to show, not the first. Start with an apostrophe... '028377310874 -- Jordon
|
|
0 |
|
Jordon
12/17/2009 7:32:09 PM
Hi Jordon, Unfortunately, that converts the number to text, which makes sorting, using it in calculations, etc, rather more complex than it needs to be. -- Cheers macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:uWX8680fKHA.2780@TK2MSFTNGP05.phx.gbl... > John Persico wrote: >> Is there anyway to make a field that has a zero at the front of it? >> >> 28377310874 >> 028377310874 >> >> Meaning, I want the second of the above to show, not the first. > > Start with an apostrophe... > > '028377310874 > > -- > Jordon
|
|
0 |
|
macropod
12/17/2009 10:09:32 PM
macropod wrote: > Hi Jordon, > > Unfortunately, that converts the number to text, which makes sorting, > using it in calculations, etc, rather more complex than it needs to be. When you top post and use a sig delimiter, a reply to your post strips out everything below your signature and makes it difficult to understand. If you insist on top posting you shouldn't use the delimiter. Someone reading your post tomorrow, that had read my post today, won't have any idea what you're talking about. I can't imagine having to do calculations on a number that isn't a number. What kind of numbers starts with a zero besides account numbers, product codes and that sort of thing? Sorting would be an issue though (if a sort is even needed). -- Jordon
|
|
0 |
|
Jordon
12/17/2009 11:12:33 PM
Hi Jordon, > When you top post and use a sig delimiter, a reply > to your post strips out everything below your > signature Not in my experience ... The only thing that stripped out was what you stripped from your own post. -- Cheers macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:eTtHF42fKHA.6096@TK2MSFTNGP02.phx.gbl... > macropod wrote: >> Hi Jordon, >> >> Unfortunately, that converts the number to text, which makes sorting, >> using it in calculations, etc, rather more complex than it needs to be. > > When you top post and use a sig delimiter, a reply > to your post strips out everything below your > signature and makes it difficult to understand. If > you insist on top posting you shouldn't use the > delimiter. Someone reading your post tomorrow, > that had read my post today, won't have any idea > what you're talking about. > > I can't imagine having to do calculations on a number > that isn't a number. What kind of numbers starts with > a zero besides account numbers, product codes and that > sort of thing? Sorting would be an issue though (if a > sort is even needed). > > -- > Jordon
|
|
0 |
|
macropod
12/18/2009 3:26:10 AM
It does for any newsreader that honors that convention. Notice that your sig and everything after it is missing from this. macropod wrote: > Hi Jordon, > >> When you top post and use a sig delimiter, a reply >> to your post strips out everything below your >> signature > > > Not in my experience ... > > The only thing that stripped out was what you stripped from your own post. >
|
|
0 |
|
Bob
12/18/2009 2:13:31 PM
macropod wrote: > Hi Jordon, > >> When you top post and use a sig delimiter, a reply >> to your post strips out everything below your >> signature > > Not in my experience ... > > The only thing that stripped out was what you stripped from your own post. > I haven't stripped anything from this. My newsreader did. Newsreaders that honor a sig delimiter strip everything after a dash dash space. That's its intended purpose. http://email.about.com/cs/netiquettetips/qt/et030601.htm -- Jordon
|
|
0 |
|
Jordon
12/18/2009 3:23:50 PM
See Chip Pearson website for information regarding "Top or Bottom " posting. I prefer top posting.(Nothing is missing) http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.asp Regards John "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:OdOZ0W$fKHA.3792@TK2MSFTNGP02.phx.gbl... > macropod wrote: >> Hi Jordon, >> >>> When you top post and use a sig delimiter, a reply >>> to your post strips out everything below your >>> signature >> >> Not in my experience ... >> >> The only thing that stripped out was what you stripped from your own post. >> > > I haven't stripped anything from this. My newsreader did. > Newsreaders that honor a sig delimiter strip everything > after a dash dash space. That's its intended purpose. > > http://email.about.com/cs/netiquettetips/qt/et030601.htm > > -- > Jordon
|
|
0 |
|
John
12/18/2009 6:33:12 PM
I don't really care one way or the other so long as everyone is doing the same thing. My only issue was his use if a sig delimiter while top posting. News readers that know what to do with a sig delimiter strip everything below it during a reply so it removes all of the conversation except what was last posted. During my reply to his post my news reader stripped everything after his signature, leaving only his post. In order for me to keep the relevant parts of the thread it would have required me to copy and paste text from his message into my reply, which I won't do. It is a little odd that he's using a sig delimiter with a news reader that doesn't know what to do with one. Jordon John wrote: > See Chip Pearson website for information regarding "Top or Bottom " > posting. > I prefer top posting.(Nothing is missing) > http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.asp > Regards > John > > "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message > news:OdOZ0W$fKHA.3792@TK2MSFTNGP02.phx.gbl... >> macropod wrote: >>> Hi Jordon, >>> >>>> When you top post and use a sig delimiter, a reply >>>> to your post strips out everything below your >>>> signature >>> >>> Not in my experience ... >>> >>> The only thing that stripped out was what you stripped from your own >>> post. >>> >> >> I haven't stripped anything from this. My newsreader did. >> Newsreaders that honor a sig delimiter strip everything >> after a dash dash space. That's its intended purpose. >> >> http://email.about.com/cs/netiquettetips/qt/et030601.htm >> >> -- >> Jordon >
|
|
0 |
|
Jordon
12/18/2009 7:39:08 PM
Hi Jordon, The convention in these forums is top posting and the overwhelming majority of posters don't use newsreaders that strip everything below the sig delimiter ... -- Cheers macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:e2ahelBgKHA.2260@TK2MSFTNGP06.phx.gbl... >I don't really care one way or the other so long as everyone > is doing the same thing. My only issue was his use if a sig > delimiter while top posting. News readers that know what to > do with a sig delimiter strip everything below it during a > reply so it removes all of the conversation except what was > last posted. During my reply to his post my news reader > stripped everything after his signature, leaving only his > post. In order for me to keep the relevant parts of the thread > it would have required me to copy and paste text from his > message into my reply, which I won't do. > > It is a little odd that he's using a sig delimiter with a news > reader that doesn't know what to do with one. > > Jordon > > John wrote: >> See Chip Pearson website for information regarding "Top or Bottom " >> posting. >> I prefer top posting.(Nothing is missing) >> http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.asp >> Regards >> John >> >> "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message >> news:OdOZ0W$fKHA.3792@TK2MSFTNGP02.phx.gbl... >>> macropod wrote: >>>> Hi Jordon, >>>> >>>>> When you top post and use a sig delimiter, a reply >>>>> to your post strips out everything below your >>>>> signature >>>> >>>> Not in my experience ... >>>> >>>> The only thing that stripped out was what you stripped from your own >>>> post. >>>> >>> >>> I haven't stripped anything from this. My newsreader did. >>> Newsreaders that honor a sig delimiter strip everything >>> after a dash dash space. That's its intended purpose. >>> >>> http://email.about.com/cs/netiquettetips/qt/et030601.htm >>> >>> -- >>> Jordon >> >
|
|
0 |
|
macropod
12/21/2009 12:22:12 AM
macropod wrote: > The convention in these forums is top posting and the overwhelming > majority of posters don't use newsreaders that strip everything below > the sig delimiter ... Forums? This is a news group. What forums are you talking about? I've been reading posts from the Microsoft Usenet server, microsoft.public, for over 12 years. And like I said, I don't really care one way or another about top vs. bottom posting as long as everyone does the same thing. Please enlighten me... Why do you use a sig delimiter? It screws up other peoples replies to your posts, but only when you top post. It doesn't when you bottom post. Since you say that the convention in these "forums" is top posting why are you intentionally doing something that destroys the flow of a thread? -- Jordon
|
|
0 |
|
Jordon
12/21/2009 4:38:12 PM
OK it's a newsgroup. > Why do you use a sig delimiter? Because the newsreader that comes with Windows inserts it automatically when one uses a signature. > It screws up other peoples replies to your posts Not if they're using the newsreader that comes with Windows - which is what the overwhelming majority of posters here use. Only those who use something that doesn't respect the conventions in these NGs have problems. Check it out - I am far from being the only one in these NGs who uses sigs and top-posting. -- Cheers macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:OFIRWulgKHA.5604@TK2MSFTNGP04.phx.gbl... > macropod wrote: >> The convention in these forums is top posting and the overwhelming >> majority of posters don't use newsreaders that strip everything below >> the sig delimiter ... > > Forums? This is a news group. What forums are you > talking about? I've been reading posts from the > Microsoft Usenet server, microsoft.public, for over > 12 years. And like I said, I don't really > care one way or another about top vs. bottom > posting as long as everyone does the same thing. > > Please enlighten me... > > Why do you use a sig delimiter? It screws up other > peoples replies to your posts, but only when you > top post. It doesn't when you bottom post. Since > you say that the convention in these "forums" is > top posting why are you intentionally doing something > that destroys the flow of a thread? > > -- > Jordon > > >
|
|
0 |
|
macropod
12/21/2009 9:22:50 PM
macropod wrote: > OK it's a newsgroup. >> Why do you use a sig delimiter? > Because the newsreader that comes with Windows inserts it automatically > when one uses a signature. >> It screws up other peoples replies to your posts > Not if they're using the newsreader that comes with Windows - which is > what the overwhelming majority of posters here use. Only those who use > something that doesn't respect the conventions in these NGs have > problems. Check it out - I am far from being the only one in these NGs > who uses sigs and top-posting. Well, would you look at that? You didn't top post and I can see my previous remarks that you've replied to after I hit reply. Do you see what I'm getting at? I have no idea what the current Windows is using for a news reader but it has to be something like Outlook Express. What's it called? Windows Mail or something like that? Are you saying that it inserts a sig delimiter, by default, but it has no way to deal with a sig delimiters intended purpose? This post seems to suggest just that... http://www.vistax64.com/vista-mail/40374-why-doesnt-mail-strip-signatures-usenet-when-doing-reply.html Overwhelming majority? Just check the User Agent in the headers of posts from a lot of the long time posters here and you'll see that they are in fact using something other than Windows Mail. Dave Peterson, Gord Dibben, Chip Pearson, Pete_UK, Don Guillett, none of them are using Windows Mail. They all use something that honors a sig delimiter. -- Jordon
|
|
0 |
|
Jordon
12/22/2009 12:06:45 AM
Both Outlook Express and Windows Mail apply sig delimiters and neither strips out anything below the delimiter. As for the 'intended purpose', I guess that's just a matter of interpretation - MS apparently doesn't interpret it the way you do. Have a nice day. -- Cheers macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:%23fXK$opgKHA.2780@TK2MSFTNGP05.phx.gbl... > macropod wrote: >> OK it's a newsgroup. > >>> Why do you use a sig delimiter? > >> Because the newsreader that comes with Windows inserts it automatically >> when one uses a signature. > >>> It screws up other peoples replies to your posts > >> Not if they're using the newsreader that comes with Windows - which is >> what the overwhelming majority of posters here use. Only those who use >> something that doesn't respect the conventions in these NGs have >> problems. Check it out - I am far from being the only one in these NGs >> who uses sigs and top-posting. > > Well, would you look at that? You didn't top post and > I can see my previous remarks that you've replied to > after I hit reply. Do you see what I'm getting at? > > I have no idea what the current Windows is using for a > news reader but it has to be something like Outlook > Express. What's it called? Windows Mail or something > like that? Are you saying that it inserts a sig > delimiter, by default, but it has no way to deal with > a sig delimiters intended purpose? This post seems to > suggest just that... > > http://www.vistax64.com/vista-mail/40374-why-doesnt-mail-strip-signatures-usenet-when-doing-reply.html > > Overwhelming majority? Just check the User Agent in the > headers of posts from a lot of the long time posters > here and you'll see that they are in fact using something > other than Windows Mail. Dave Peterson, Gord Dibben, Chip > Pearson, Pete_UK, Don Guillett, none of them are using > Windows Mail. They all use something that honors a sig > delimiter. > > -- > Jordon
|
|
0 |
|
macropod
12/22/2009 2:20:06 AM
macropod wrote: > Both Outlook Express and Windows Mail apply sig delimiters and neither > strips out anything below the delimiter. As for the 'intended purpose', > I guess that's just a matter of interpretation - MS apparently doesn't > interpret it the way you do. Just what do you think a sig delimiter is for? To look pretty? Is google broken for you? You didn't read the link I provided, did you? Try this one... http://lmgtfy.com/?q=purpous+signature+delimiter -- Jordon
|
|
0 |
|
Jordon
12/22/2009 3:14:47 PM
> You didn't read the link I provided, did you? Sure I did. And it says *nothing* about deleting text other than the sig: "It would be great if a common signature delimiter could become a standard as well. Then email clients could automatically delete the signature from the quoted text" If you're going to use a newsreader that lamely clips other stuff too, don't complain about those that don't. -- macropod [Microsoft MVP - Word] "Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message news:u78MZkxgKHA.5792@TK2MSFTNGP05.phx.gbl... > macropod wrote: >> Both Outlook Express and Windows Mail apply sig delimiters and neither >> strips out anything below the delimiter. As for the 'intended purpose', >> I guess that's just a matter of interpretation - MS apparently doesn't >> interpret it the way you do. > > Just what do you think a sig delimiter is for? To > look pretty? Is google broken for you? > > You didn't read the link I provided, did you? Try > this one... > > http://lmgtfy.com/?q=purpous+signature+delimiter > > -- > Jordon
|
|
0 |
|
macropod
12/23/2009 12:19:24 AM
macropod wrote: >> You didn't read the link I provided, did you? > > Sure I did. And it says *nothing* about deleting text other than the sig: > "It would be great if a common signature delimiter could become a > standard as well. Then email clients could automatically delete the > signature from the quoted text" If you had read it you would have read the following... ~~~~~~~~~~~ It would be great if a common signature delimiter could become a standard as well. Then email clients could automatically delete the signature from the quoted text when you compose a reply, for example. Use the Standard Signature Delimiter The "standard" that is widely used on Usenet, but also with email, is * "-- " (two dashes followed by a white-space character) on a line by itself. ~~~~~~~~~~~~ It does that by removing the delimiter and everything below it. Since usenet netiquette has dictated, since it's inception, bottom posting, traditionally signatures go at the bottom. Removing the delimiter and everything below it never removes anything else in the message. > If you're going to use a newsreader that lamely clips other stuff too, > don't complain about those that don't. How is a news reader suppose to know when the signature ends? I dare you to find me a newsreader that will only remove a signature in the middle of a post leaving what's above it and what's below intact. There aren't any. Geeze. Why are you making this so difficult? Open your eyes, read a little about usenet history and you'll understand. I can tell, you're one of those people that can never admit you're wrong. I'm done beating a dead horse. Believe what you want to believe.
|
|
0 |
|
Jordon
12/23/2009 3:33:48 PM
Similar Artilces:
Averaging non consecutive cells excluding zero's
how can I average non consecutive cells in a row excluding zero's eg. A1=2 D1=6 F1=0 J1=4 all cells in the range (A1:K1)have values in them but only the sum of cetain cell need to be averaged any help is a appreciated. =SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(J1<>0,1,0)+IF(F1<>0,1,0)+IF (J1<>0,1,0)) I didn't condtiional the sum as adding zero wouldn't change anything. Sorry, that should be: =SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(D1<>0,1,0)+IF(F1<>0,1,0)+IF (J1<>0,1,0)) A little too much copy & paste in the formula bar in the orig...
Force Uninstall
All, A coupla questions . . . Suppose I just want to forcefully uninstall Exchange 2000 from a server, and an org. Currently the uninstall fails because (as mentioned in a previous post) I have users that still show as being on that server, however the store that those users were on is no longer there (because of a disaster). Is there some way to force the uninstall? Another question. I have two healthy clustered machines running currently. Suppose I just remove the object for the old exchange server from the ESM? Assuming I have performed all of the other steps steps (like moving o...
forcing user input into cells
howdy all... i'm new to doing stuff with excel have a bit of a background in javascript programming what i'm trying to do is force a user to enter something, anything int a specific cell if the user tries to tab past the cell and leave it blank, i want a error to come up, telling them that they have to enter something befor they can continue on with entry into other cells any help would be much appreciated -Thank -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=164...
Force quit
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Closing Word get error message "A file error has occurred. Check your network connections or make sure the disk is properly inserted" Am then required to use force quit. Anybody have solution? Make sure Office & OS X are fully updated. If they are & you still encounter the problem, crashing on Quit is usually the result of one of theses causes: Need to repair disk permissions Damaged preference files Corrupt Normal.dotm template See this page for the respective links in t...
Negative to zero code
I went in and formatted a cell to: 0;"0";0 so that all negative numbers will be converted to zero. I have a textbox on a userform that I need to do the same thing. What code would I use? I tried this but it doesn't work: txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""") Any help would be greatly appriciated. Thanks, ~Gabe This worked for me: Sub gh() Sheets(1).TextBox1.Text = 123.751 MsgBox "OK" Sheets(1).TextBox1.Text = _ Format(Sheet1.TextBox1.Text, "#,##0.00") End Sub &quo...
Force REPLY to go back to distribution list
I have an issue whereby a department is insisting that they want their mailing lists marked so that any time anyone clicks REPLY (not "REPLY ALL" - they refuse that as an acceptable solution, it must be REPLY) that the reply message goes back to the distribution list and not the message originator. They further insist that this capability must occur on the mail server (and not on Outlookclient side), since they use a multitude of different mail clients (many on Unix/Linux) and therefore it is a server-side responsibility to make this happen. They say that this capability exists in ...
Forcing Appointments
I use outlook to schedule appointments with my customers with specific employees. I have a scheduling department that does this scheduling via outlook. The employees have no option to reject or modify these appointments; therefore, I would like to force this on their calendar with them having to accept it and not allowing them to modify it. Additionally, I need to know real time once they are scheduled. Right now I don't know they are scheduled until they accept and that sometimes takes too long and then they are inadvertently double booked by the scheduling department. So how do ...
suppress zero values in line charts?
How can I suppress the plotting of zero values in line charts? The source cells have a formula that evaluates as zero for future dates in the date range. Rather than getting the formula to evaluate to zero, get it to evaluate to the N/A# error =IF(Date<Today,NA(),Value) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Cruickshank Brothers" <Cruickshank Brothers@discussions.microsoft.com> wrote in message news:C5AB5CB7-05A5-4F3C-ACFF-A81404778E12@microsoft.com... > How can I suppress the plotting of zero values...
force
...
Forcing an account
Hi All Is there anyway to get Outlook 2002 and/or 2003 to force to use a certain mail account for certain contacts? For example for contact A, B and C I want to always send new messages, replies and forwards via mail account 1 and for contact D, E and F I want to do the same using mail account 2. I don't want this to deviate even if one of these contacts has sent an email to an alternate mail account. Even though I set my main account as the default, when certain emails come in on my other accounts I can't force my replies or forwards to go to a certain account. Are there any hidd...
Force PasteSpecialValues in Excel 2007
There was a very nice piece of code written by Aaron Bush (Oorang on MrExcel) in 2007 which disabled cut and copy execution in excel, forcing the copy to copy only values but maintaining the ability to undo (http://www.vbaexpress.com/kb/getarticle.php?kb_id=957). Unfortunately, this code fails in several ways in Excel 2007. 1/ It does not disable ribbon cut/copy/paste features 2/ Paste still functions normally when using <enter> to paste the previously copied cell 3/ If you are copying from a locked cell in a protected sheet, the code generates a 1004 error (though the p...
Forcing an ActiveX control to serialize it's properties.
Is there a way to force an activeX control to serialize it's properties? Kurt "Kurt" <k_nojunk@larimore.net> wrote in message news:OqS15%23p3DHA.3224@tk2msftngp13.phx.gbl... > Is there a way to force an activeX control to serialize it's properties? > > Kurt > > Kurt, Try getting a pointer to the IPersistStorage interface via QueryInterface. Then call the method IPersistStorage::Save which needs an IStorage* to specify where the properties will be saved to. ...
How force table update when no bound field updated?
I have a main form with a 'Balance Due' field. There are two balance due controls, one unbound (displayed) and one bound (hidden). I also have a subform which is a continuous form containing payment data. When payment details are entered or changed, the balance due on the main form and in the bound table needs to be updated. The total of payments is summed in the subform footer and the balance due (unbound control) refers to this subform total. In main Form BeforeUpdate, I copy the unbound balance due to the bound balance due. The problem is that when only payment data is...
Force combo box selection before moving off field
I've searched through the group but I can't seem to find exactly what I'm looking for. Using 2000 ... I have a combo box on a form. The row source is a simple query. The control source is a long integer (link to an autonumber). Form is based on a table. I don't want the user to be able to move off the CB (combo box) unless they select something from the list (or type in a new item). I've tried making the control source required in the table. This won't let them move off the RECORD until they do something in the CB. I want them stopped at the CB. I set up...
Restricting Zero Quantity Transaction Posting
RMS should not allow a cashier to zero a quantity (creating a zero sale) and tender the sale for zero dollars allowing the cash drawer to open. I know you can restrict changing quantities for items (which is not what I want and is not the solution I am suggesting). I want RMS to not allow a cashier to enter an item with zero quantity and zero total cost to hit "Tender" or "Enter" then enter "0" for the tender received and have the cash drawer open. This allows cashiers to ring up product, tell the customer what they owe, zero out the quantity (removing th...
Force Secure
Is there any way to force the system to secure itself after a cashier uses the time clock? Great Question. But No. "Scott S" wrote: > Is there any way to force the system to secure itself after a cashier uses > the time clock? > > > ...
Show zeros for null in crosstab query
Hi. I created an crosstab query and would like null cells to show a zero. is there a way to do this in the property sheet or should i use an expression? Problem is there are many columns in this crosstab and it may be cumbersome to put an expresssion in each column. Thanks for any help! Switch your query to SQL view. The first line should be something like Transform Max(SomeField) As [The Value] Change that to Transform Nz(Max(SomeField), 0) As [The Value] -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Krisp...
Creating an event by force.
Hello, I want to create an event by force. How can I do that ? (for example : ToolStrip.ItemClicked event) Thanks :) To the dark side this path leads. Explain your request you should. Yoda On 6/10/2010 6:06 PM, Mr. X. wrote: > Hello, > I want to create an event by force. > How can I do that ? > > (for example : ToolStrip.ItemClicked event) > > Thanks :) Do you mean SomeToolstrip.Items (0).PerformClick()? -- Mike Thanks, specific, this help. I need more generic solution (I would like to do any event, not only the PerformClick event). ...
Possible to force output format of empty elements?
I have built a small integration app using VS .NET 2003 that extracts orderinformation from a 'webshop'. Extracting the orderinformation works fine. Appending the order elements in the XmlDocument was also done in a jiffy. The final step is to save the document to disk and then ship it to another system using ftp. The xml orderfile produced must fit a set specification of the recieving system. That specification states that empty elements must be formated in one line using a long format for empty elements. It should look like this: <address1></address1> And not like...
Reference shows zero
Hi, I have a cell reference in cell B1 (=A1). If A1 is empty B1 shows "0". Why isn't B1 empty if A1 is? Both cells have format General. /Tommy "Why isn't B1 empty if A1 is?" is the same as "Why does the sun rise in the east?" That's just the way it is. Modify your formula to: =if(a1="","",a1) Regards, Fred "TommyD" <TommyD@discussions.microsoft.com> wrote in message news:CEFD9860-E038-497A-B796-5B0FC5C87B14@microsoft.com... > Hi, > > I have a cell reference in cell B1 (=A1...
Excel SaveAs to force overwrite of extant file
I'm using Excel 2007 in 2003 compatibility mode. I have a VBA macro that saves a dynamic file name using SaveAs, but the file name may or may not already exist. I wish to force the SaveAs command to overwrite any pre-existing file automatically (without user intervention to confirm that I wish to overwrite the extant file). I'm sure I could do this in Excel 2003 but can I do it in Excel 2007 and in compatibility mode? Wrap your code in Application.DisplayAlerts = False your save code Application.DisplayAlerts = True Gord Dibben MS Excel MVP On Fri,...
Hiding Zeros in Stacked Bar Charts
Hi How do I hide Zero values in a stacked Bar Chart? I want to display amounts in the bars but not when they have zero values. Could you please help me. Regards Darshan Hi, I assume you are referring to the data labels, in which case use a custom number format to suppress zero. General;General; Double click the data labels and use the Number tab to add this number format. Cheers Andy Darshan72 wrote: > Hi > > How do I hide Zero values in a stacked Bar Chart? > > I want to display amounts in the bars but not when they have zero values. > Could you ...
Forced quit
Version: 2008 Operating System: Mac OS X 10.1 (Puma) Processor: Intel Frequently excel freezes and I must use force quit. The message is displayed that auto recover could no longer save my information. Having to force quit frequently is very annoying. I would be willing to turn off autosave if this would solve the problem. Are there other solutions? What should I do? ...
Forcing Outbound SMTP to bind to a particular address
This is likely addressed elsewhere, but for the life of me i can't find it. We are running an Exchange 2003 active/passive cluster. Inbound SMTP works great, but outbound SMTP is binding to the wrong IP address. Instead of binding to the virtual IP, it binds to the machine IP. The virtual IP address is set up to be natted correctly, but the individual machines are hide-natted using a different public IP address, which isn't listed in DNS. Is there a way to force the Source IP address on outbound SMTP connections? Is this clear as mud? Thanks in advance, Earl You should have...
Value of Less Than Zero to Equal Zero
Hello: I am trying to format my answers in cells of a worksheet. I have an equation that uses addition, subtraction, multiplication and division. This equation has dependent variables in other cells and when these cells are filled in with data, the original equation yields a number. Unfortunately, if one of the cells is not used, there is still a value reported by the original equation. As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the result will be equal to 5.5. But if cell A2 does not have a value, the value will report -0.5. Here is the problem. Well, I think I have t...
how can I average non consecutive cells in a row excluding zero's eg. A1=2 D1=6 F1=0 J1=4 all cells in the range (A1:K1)have values in them but only the sum of cetain cell need to be averaged any help is a appreciated. =SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(J1<>0,1,0)+IF(F1<>0,1,0)+IF (J1<>0,1,0)) I didn't condtiional the sum as adding zero wouldn't change anything. Sorry, that should be: =SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(D1<>0,1,0)+IF(F1<>0,1,0)+IF (J1<>0,1,0)) A little too much copy & paste in the formula bar in the orig...
Force Uninstall
All, A coupla questions . . . Suppose I just want to forcefully uninstall Exchange 2000 from a server, and an org. Currently the uninstall fails because (as mentioned in a previous post) I have users that still show as being on that server, however the store that those users were on is no longer there (because of a disaster). Is there some way to force the uninstall? Another question. I have two healthy clustered machines running currently. Suppose I just remove the object for the old exchange server from the ESM? Assuming I have performed all of the other steps steps (like moving o...
forcing user input into cells
howdy all... i'm new to doing stuff with excel have a bit of a background in javascript programming what i'm trying to do is force a user to enter something, anything int a specific cell if the user tries to tab past the cell and leave it blank, i want a error to come up, telling them that they have to enter something befor they can continue on with entry into other cells any help would be much appreciated -Thank -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=164...
Force quit
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Closing Word get error message "A file error has occurred. Check your network connections or make sure the disk is properly inserted" Am then required to use force quit. Anybody have solution? Make sure Office & OS X are fully updated. If they are & you still encounter the problem, crashing on Quit is usually the result of one of theses causes: Need to repair disk permissions Damaged preference files Corrupt Normal.dotm template See this page for the respective links in t...
Negative to zero code
I went in and formatted a cell to: 0;"0";0 so that all negative numbers will be converted to zero. I have a textbox on a userform that I need to do the same thing. What code would I use? I tried this but it doesn't work: txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""") Any help would be greatly appriciated. Thanks, ~Gabe This worked for me: Sub gh() Sheets(1).TextBox1.Text = 123.751 MsgBox "OK" Sheets(1).TextBox1.Text = _ Format(Sheet1.TextBox1.Text, "#,##0.00") End Sub &quo...
Force REPLY to go back to distribution list
I have an issue whereby a department is insisting that they want their mailing lists marked so that any time anyone clicks REPLY (not "REPLY ALL" - they refuse that as an acceptable solution, it must be REPLY) that the reply message goes back to the distribution list and not the message originator. They further insist that this capability must occur on the mail server (and not on Outlookclient side), since they use a multitude of different mail clients (many on Unix/Linux) and therefore it is a server-side responsibility to make this happen. They say that this capability exists in ...
Forcing Appointments
I use outlook to schedule appointments with my customers with specific employees. I have a scheduling department that does this scheduling via outlook. The employees have no option to reject or modify these appointments; therefore, I would like to force this on their calendar with them having to accept it and not allowing them to modify it. Additionally, I need to know real time once they are scheduled. Right now I don't know they are scheduled until they accept and that sometimes takes too long and then they are inadvertently double booked by the scheduling department. So how do ...
suppress zero values in line charts?
How can I suppress the plotting of zero values in line charts? The source cells have a formula that evaluates as zero for future dates in the date range. Rather than getting the formula to evaluate to zero, get it to evaluate to the N/A# error =IF(Date<Today,NA(),Value) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Cruickshank Brothers" <Cruickshank Brothers@discussions.microsoft.com> wrote in message news:C5AB5CB7-05A5-4F3C-ACFF-A81404778E12@microsoft.com... > How can I suppress the plotting of zero values...
force
...
Forcing an account
Hi All Is there anyway to get Outlook 2002 and/or 2003 to force to use a certain mail account for certain contacts? For example for contact A, B and C I want to always send new messages, replies and forwards via mail account 1 and for contact D, E and F I want to do the same using mail account 2. I don't want this to deviate even if one of these contacts has sent an email to an alternate mail account. Even though I set my main account as the default, when certain emails come in on my other accounts I can't force my replies or forwards to go to a certain account. Are there any hidd...
Force PasteSpecialValues in Excel 2007
There was a very nice piece of code written by Aaron Bush (Oorang on MrExcel) in 2007 which disabled cut and copy execution in excel, forcing the copy to copy only values but maintaining the ability to undo (http://www.vbaexpress.com/kb/getarticle.php?kb_id=957). Unfortunately, this code fails in several ways in Excel 2007. 1/ It does not disable ribbon cut/copy/paste features 2/ Paste still functions normally when using <enter> to paste the previously copied cell 3/ If you are copying from a locked cell in a protected sheet, the code generates a 1004 error (though the p...
Forcing an ActiveX control to serialize it's properties.
Is there a way to force an activeX control to serialize it's properties? Kurt "Kurt" <k_nojunk@larimore.net> wrote in message news:OqS15%23p3DHA.3224@tk2msftngp13.phx.gbl... > Is there a way to force an activeX control to serialize it's properties? > > Kurt > > Kurt, Try getting a pointer to the IPersistStorage interface via QueryInterface. Then call the method IPersistStorage::Save which needs an IStorage* to specify where the properties will be saved to. ...
How force table update when no bound field updated?
I have a main form with a 'Balance Due' field. There are two balance due controls, one unbound (displayed) and one bound (hidden). I also have a subform which is a continuous form containing payment data. When payment details are entered or changed, the balance due on the main form and in the bound table needs to be updated. The total of payments is summed in the subform footer and the balance due (unbound control) refers to this subform total. In main Form BeforeUpdate, I copy the unbound balance due to the bound balance due. The problem is that when only payment data is...
Force combo box selection before moving off field
I've searched through the group but I can't seem to find exactly what I'm looking for. Using 2000 ... I have a combo box on a form. The row source is a simple query. The control source is a long integer (link to an autonumber). Form is based on a table. I don't want the user to be able to move off the CB (combo box) unless they select something from the list (or type in a new item). I've tried making the control source required in the table. This won't let them move off the RECORD until they do something in the CB. I want them stopped at the CB. I set up...
Restricting Zero Quantity Transaction Posting
RMS should not allow a cashier to zero a quantity (creating a zero sale) and tender the sale for zero dollars allowing the cash drawer to open. I know you can restrict changing quantities for items (which is not what I want and is not the solution I am suggesting). I want RMS to not allow a cashier to enter an item with zero quantity and zero total cost to hit "Tender" or "Enter" then enter "0" for the tender received and have the cash drawer open. This allows cashiers to ring up product, tell the customer what they owe, zero out the quantity (removing th...
Force Secure
Is there any way to force the system to secure itself after a cashier uses the time clock? Great Question. But No. "Scott S" wrote: > Is there any way to force the system to secure itself after a cashier uses > the time clock? > > > ...
Show zeros for null in crosstab query
Hi. I created an crosstab query and would like null cells to show a zero. is there a way to do this in the property sheet or should i use an expression? Problem is there are many columns in this crosstab and it may be cumbersome to put an expresssion in each column. Thanks for any help! Switch your query to SQL view. The first line should be something like Transform Max(SomeField) As [The Value] Change that to Transform Nz(Max(SomeField), 0) As [The Value] -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Krisp...
Creating an event by force.
Hello, I want to create an event by force. How can I do that ? (for example : ToolStrip.ItemClicked event) Thanks :) To the dark side this path leads. Explain your request you should. Yoda On 6/10/2010 6:06 PM, Mr. X. wrote: > Hello, > I want to create an event by force. > How can I do that ? > > (for example : ToolStrip.ItemClicked event) > > Thanks :) Do you mean SomeToolstrip.Items (0).PerformClick()? -- Mike Thanks, specific, this help. I need more generic solution (I would like to do any event, not only the PerformClick event). ...
Possible to force output format of empty elements?
I have built a small integration app using VS .NET 2003 that extracts orderinformation from a 'webshop'. Extracting the orderinformation works fine. Appending the order elements in the XmlDocument was also done in a jiffy. The final step is to save the document to disk and then ship it to another system using ftp. The xml orderfile produced must fit a set specification of the recieving system. That specification states that empty elements must be formated in one line using a long format for empty elements. It should look like this: <address1></address1> And not like...
Reference shows zero
Hi, I have a cell reference in cell B1 (=A1). If A1 is empty B1 shows "0". Why isn't B1 empty if A1 is? Both cells have format General. /Tommy "Why isn't B1 empty if A1 is?" is the same as "Why does the sun rise in the east?" That's just the way it is. Modify your formula to: =if(a1="","",a1) Regards, Fred "TommyD" <TommyD@discussions.microsoft.com> wrote in message news:CEFD9860-E038-497A-B796-5B0FC5C87B14@microsoft.com... > Hi, > > I have a cell reference in cell B1 (=A1...
Excel SaveAs to force overwrite of extant file
I'm using Excel 2007 in 2003 compatibility mode. I have a VBA macro that saves a dynamic file name using SaveAs, but the file name may or may not already exist. I wish to force the SaveAs command to overwrite any pre-existing file automatically (without user intervention to confirm that I wish to overwrite the extant file). I'm sure I could do this in Excel 2003 but can I do it in Excel 2007 and in compatibility mode? Wrap your code in Application.DisplayAlerts = False your save code Application.DisplayAlerts = True Gord Dibben MS Excel MVP On Fri,...
Hiding Zeros in Stacked Bar Charts
Hi How do I hide Zero values in a stacked Bar Chart? I want to display amounts in the bars but not when they have zero values. Could you please help me. Regards Darshan Hi, I assume you are referring to the data labels, in which case use a custom number format to suppress zero. General;General; Double click the data labels and use the Number tab to add this number format. Cheers Andy Darshan72 wrote: > Hi > > How do I hide Zero values in a stacked Bar Chart? > > I want to display amounts in the bars but not when they have zero values. > Could you ...
Forced quit
Version: 2008 Operating System: Mac OS X 10.1 (Puma) Processor: Intel Frequently excel freezes and I must use force quit. The message is displayed that auto recover could no longer save my information. Having to force quit frequently is very annoying. I would be willing to turn off autosave if this would solve the problem. Are there other solutions? What should I do? ...
Forcing Outbound SMTP to bind to a particular address
This is likely addressed elsewhere, but for the life of me i can't find it. We are running an Exchange 2003 active/passive cluster. Inbound SMTP works great, but outbound SMTP is binding to the wrong IP address. Instead of binding to the virtual IP, it binds to the machine IP. The virtual IP address is set up to be natted correctly, but the individual machines are hide-natted using a different public IP address, which isn't listed in DNS. Is there a way to force the Source IP address on outbound SMTP connections? Is this clear as mud? Thanks in advance, Earl You should have...
Value of Less Than Zero to Equal Zero
Hello: I am trying to format my answers in cells of a worksheet. I have an equation that uses addition, subtraction, multiplication and division. This equation has dependent variables in other cells and when these cells are filled in with data, the original equation yields a number. Unfortunately, if one of the cells is not used, there is still a value reported by the original equation. As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the result will be equal to 5.5. But if cell A2 does not have a value, the value will report -0.5. Here is the problem. Well, I think I have t...