Display plus or minus signs on calculated table field

[Fred]

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

 

[Fred]

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

 

[Doug Robbins - Word MVP]

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]

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

 

[Doug Robbins - Word MVP]

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]

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