A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Display plus or minus signs on calculated table field



 
 
Thread Tools Display Modes
  #1  
Old January 19th 07, 07:34 AM posted to microsoft.public.word.tables
Fred
external usenet poster
 
Posts: 6
Default Display plus or minus signs on calculated table field

I wasn't sure whether this query counted as a table query or a VB
query, so have posted here first.

Using Word 97, I have a word table, that is set to protected (Forms).
In the table there are, amongst others, 2 columns of dates and a third
column which I need to calculate the difference between the dates in
the other two columns.

I have defined the date columns with a Type of Date, both have bookmark
names ITarg1 ...n and CTarg1 ...n the third column is has a Type of
Number, a bookmark of MSDD1 ...n and a format of +#,##0

On exit from the CTarg column I run a macro (DD_MSDD1 ...n) to
calculate the difference and put the result in the third column.

Sub DD_MSDD1()
MilestoneDateDiff "1" 'call to common routine to calc diff for MSDD1
End Sub

Sub MilestoneDateDiff(DD)

Initial = DateValue(ActiveDocument.FormFields("ITarg" & DD).Result)
Current = DateValue(ActiveDocument.FormFields("CTarg" & DD).Result)
days = Format(Current - Initial, ("+#,##0 ;-#,##0"))
ActiveDocument.Unprotect
ActiveDocument.FormFields("MSDD" & DD).Result = days
ActiveDocument.Protect wdAllowOnlyFormFields, True
End Sub

What I need to achieve is the difference figure in the third column to
display either as +ddd or -ddd, so far all I can achieve is ddd or
-ddd. I have tried various formats, both within the vb and the cell
format to no avail, ending up with, "(+14)" when there is a negative
difference, (go figure !) or "+28" when there is a positive difference
when I had set the MSDD1 cell with a format of +#,##0;(-#,##0)

I would also like to introduce some degree of checking that there are
values in both ITarg and CTarg cells, if at all possible.

Can anyone help me out with this problem please ?

Thanks and regards
Fred

Ads
  #2  
Old January 19th 07, 07:42 AM posted to microsoft.public.word.tables
Fred
external usenet poster
 
Posts: 6
Default Display plus or minus signs on calculated table field


One thing I forgot to say, when MSDD1 ...n has the format of +#,##0,
for a negative difference I get "+-14" and a positive difference gives
"+28", so i'm part way there.

Regards
Fred

  #3  
Old January 19th 07, 10:45 AM posted to microsoft.public.word.tables
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,832
Default Display plus or minus signs on calculated table field

You should be using the DateDiff() function

Also, it is not necessary to unprotect the document to use the .Result
property of a formfield.

Use the IsDate() function to check for a valid entry in the date formfields.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Fred" wrote in message
ps.com...

One thing I forgot to say, when MSDD1 ...n has the format of +#,##0,
for a negative difference I get "+-14" and a positive difference gives
"+28", so i'm part way there.

Regards
Fred



  #4  
Old January 19th 07, 11:20 AM posted to microsoft.public.word.tables
Fred
external usenet poster
 
Posts: 6
Default Display plus or minus signs on calculated table field

Hi Doug,

Thanks for the reply, I've successfully used DateDiff(), however that
leaves me with the problem of the +/- display.

I am required to show positive numbers prefixed with + and negative
prefixed with -. It seems that the default is no prefix for positive
and - for negative, I have tried the format of +#,##0, but, for a
negative difference I get "+-14" and a positive difference gives "+28",
so i'm part way there.

Thanks for the IsDate, i'll try that next.

Regards
Fred

  #5  
Old January 19th 07, 12:29 PM posted to microsoft.public.word.tables
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,832
Default Display plus or minus signs on calculated table field

Use:

If Difference 0 then
....Result = "+" & Format(Difference)
Else
....Result = Difference
End If

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Fred" wrote in message
ups.com...
Hi Doug,

Thanks for the reply, I've successfully used DateDiff(), however that
leaves me with the problem of the +/- display.

I am required to show positive numbers prefixed with + and negative
prefixed with -. It seems that the default is no prefix for positive
and - for negative, I have tried the format of +#,##0, but, for a
negative difference I get "+-14" and a positive difference gives "+28",
so i'm part way there.

Thanks for the IsDate, i'll try that next.

Regards
Fred



  #6  
Old January 22nd 07, 10:11 AM posted to microsoft.public.word.tables
Fred
external usenet poster
 
Posts: 6
Default Display plus or minus signs on calculated table field

Hi Doug,

Thanks for the reply, IsDate worked perfectly and, after some
experimenting with the additional code, I finally ended up changing the
Form Field options for the output cell from Numeric to Text and then
the + or - appeared on cue.

Thanks for your help
Regards
Fred

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mail merging formatted content donna Mailmerge 18 July 28th 06 06:20 PM
Table Format Style vs. Table Text Style WebColin Page Layout 11 December 1st 05 11:29 PM
Document Styles should be assignable to fonts in Table AutoFormat Natasha A. Tables 5 November 11th 05 05:49 PM
Text form field inside WORD table cell, odd selection behavior pwrichcreek Microsoft Word Help 1 October 11th 05 03:57 PM
Table headers/footers and layout Keith Page Layout 1 April 8th 05 07:37 PM


All times are GMT +1. The time now is 09:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.