Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Hiding zero values
I want to have a calculation field in a form show as blank (spaces) when the
value is zero. |
#2
|
|||
|
|||
DomMar,
You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#3
|
|||
|
|||
I'm sorry I was not clearer. The numeric format for the field includes a $ as
in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#4
|
|||
|
|||
DomMar.
Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#5
|
|||
|
|||
or { =(Text1+Text2) \# "$,#.00;($,#.00); " }
-- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#6
|
|||
|
|||
I tried to use Ctrl+F9 but it could not be entered. I tried with both a
calculation field and a formula field. Neither dialog box accepts ctrl+F9. My document has a macro which will update all fields in document. I also tried using keyboard {} but they did not work. Regards, DomMar "Greg Maxey" wrote: or { =(Text1+Text2) \# "$,#.00;($,#.00); " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#7
|
|||
|
|||
DomMar,
Forget the calculation field. Unprotect the document and use CTRL+F9 to create a set of field code delimiters { } type the formula and format switch directly inside the delimiters. Select the field and toggle the code. Ensure both (or all) variable formfields are set to "calculate on exit." Reprotect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I tried to use Ctrl+F9 but it could not be entered. I tried with both a calculation field and a formula field. Neither dialog box accepts ctrl+F9. My document has a macro which will update all fields in document. I also tried using keyboard {} but they did not work. Regards, DomMar "Greg Maxey" wrote: or { =(Text1+Text2) \# "$,#.00;($,#.00); " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#8
|
|||
|
|||
Greg,
Thank you I finally got it. Is there a way I can do the same for an enterable numeric text field. Hrs. * Rate = Amt. Amt was the field you pounded into my dense head. I'd like to do the same for Rate which is enterable. Regards, DomMar "Greg Maxey" wrote: DomMar, Forget the calculation field. Unprotect the document and use CTRL+F9 to create a set of field code delimiters { } type the formula and format switch directly inside the delimiters. Select the field and toggle the code. Ensure both (or all) variable formfields are set to "calculate on exit." Reprotect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I tried to use Ctrl+F9 but it could not be entered. I tried with both a calculation field and a formula field. Neither dialog box accepts ctrl+F9. My document has a macro which will update all fields in document. I also tried using keyboard {} but they did not work. Regards, DomMar "Greg Maxey" wrote: or { =(Text1+Text2) \# "$,#.00;($,#.00); " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#9
|
|||
|
|||
DomMar,
All I can suggest is an on exit macro to set the value to " " if the value on exit is "0" Sub Test() If ActiveDocument.FormFields("Text2").Result = "0" Then ActiveDocument.FormFields("Text2").Result = " " End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: Greg, Thank you I finally got it. Is there a way I can do the same for an enterable numeric text field. Hrs. * Rate = Amt. Amt was the field you pounded into my dense head. I'd like to do the same for Rate which is enterable. Regards, DomMar "Greg Maxey" wrote: DomMar, Forget the calculation field. Unprotect the document and use CTRL+F9 to create a set of field code delimiters { } type the formula and format switch directly inside the delimiters. Select the field and toggle the code. Ensure both (or all) variable formfields are set to "calculate on exit." Reprotect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I tried to use Ctrl+F9 but it could not be entered. I tried with both a calculation field and a formula field. Neither dialog box accepts ctrl+F9. My document has a macro which will update all fields in document. I also tried using keyboard {} but they did not work. Regards, DomMar "Greg Maxey" wrote: or { =(Text1+Text2) \# "$,#.00;($,#.00); " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
#10
|
|||
|
|||
Thanks you have been very Helpful.
"Greg Maxey" wrote: DomMar, All I can suggest is an on exit macro to set the value to " " if the value on exit is "0" Sub Test() If ActiveDocument.FormFields("Text2").Result = "0" Then ActiveDocument.FormFields("Text2").Result = " " End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: Greg, Thank you I finally got it. Is there a way I can do the same for an enterable numeric text field. Hrs. * Rate = Amt. Amt was the field you pounded into my dense head. I'd like to do the same for Rate which is enterable. Regards, DomMar "Greg Maxey" wrote: DomMar, Forget the calculation field. Unprotect the document and use CTRL+F9 to create a set of field code delimiters { } type the formula and format switch directly inside the delimiters. Select the field and toggle the code. Ensure both (or all) variable formfields are set to "calculate on exit." Reprotect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I tried to use Ctrl+F9 but it could not be entered. I tried with both a calculation field and a formula field. Neither dialog box accepts ctrl+F9. My document has a macro which will update all fields in document. I also tried using keyboard {} but they did not work. Regards, DomMar "Greg Maxey" wrote: or { =(Text1+Text2) \# "$,#.00;($,#.00); " } -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Greg Maxey wrote: DomMar. Again you don't need a macro. A numeric switch will work fine: { =(Text1+Text2) \# "$,#.00;-$,#.00; " } DomMar wrote: I'm sorry I was not clearer. The numeric format for the field includes a $ as in $#,##0.00;($#,##0.00). I tried using all # but the $ showed when the field was zero. Is there a macro that I can use to change the format when the value is zero? "Greg Maxey" wrote: DomMar, You can use a regulare formula field (vice calculation field) with a numeric switch to hide the zero. Example. Say you have Text1 and Text2 as the varialbles. Set both to calculate on exit. Enter the following formula field. { =(Text1+Text1) \# "#;-#; " } Note the field braces { } are entered with CTRL+F9. Once you have the field entered then toggle the code and protect the form. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. DomMar wrote: I want to have a calculation field in a form show as blank (spaces) when the value is zero. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove mergefields, keep values | Mailmerge | |||
Merging past specified values?? | Mailmerge | |||
where are the macros hiding | Microsoft Word Help | |||
Specifying CC: and BCC: Values for Mail Merge to Email | Mailmerge | |||
Linked values | Microsoft Word Help |