trying to define a date field from an other one

[An Dong]

Hello,
under outlook 2000, i'm creating a customised form based on a task
form. i want a field to be automatically updated when an other one is
filled. and i want this field to display the due date from a
predifined number of business days. but calculating the due date in a
forumla is impossible. so i wrote a function and now i wonder how
outlook could have access to it so that i could just enter my function
and the parameters as the forumla. i hope my english is good enough
for you to understand...
if it can help, i join the function i wrote.
Thank you !
An Dong

Function GetDateDeFin(dteDebut As Date, intNbreJours As Integer) As
Date
Dim dteDateVariable As Date
Dim intNbreSem As Integer
Dim intNbreJoursRestants As Integer
Dim intJourDeLaSem As Integer
Dim i As Integer
Dim test As Boolean
Dim dteTest As Date

' nombre de semaines à ajouter d'après le nombre de jours ouvrés
en
' paramètre
intNbreSem = intNbreJours / 5

' ajout des semaines à aujourd'hui
dteDateVariable = DateAdd("w", intNbreSem, dteDebut)

' nombre de jours ouvrés restants après déduction des semaines
complètes
intNbreJoursRestants = intNbreJours
Do While intNbreJoursRestants >= 5
intNbreJoursRestants = intNbreJoursRestants - 5
Loop

'test si un des intNbreJoursRestants jours à ajouter est un samedi
i = 1
test = false
dteTest = dteDateVariable
Do While i <= intNbreJoursRestants and test = false
If Weekday(dteTest) = Saturday Then test = true
End If
i = i+1
dteTest = DateAdd("d", 1, dteTest)
Loop

'ajout du week-end si il y a un week-end
If test = true Then dteDateVariable = DateAdd("d", 2,
dteDateVariable)

' ajout des jours restants
dteDateVariable = DateAdd("d", intNbreJoursRestants,
dteDateVariable)

GetDateDeFin = dteDateVariable
End Function

 

[Sue Mosher [MVP-Outlook]]

Why do you say that calculating the due date in a formula is impossible?
That's exactly what the DateAdd() function is designed for.

 

[An Dong]

Hello,
Yes, sure, but how can you write a formula saying : i want you to add
two days to the due date for each week-end between the start date and
the due date, and if because of this operation one or more week end is
added between the start date and the due date, add two days to the due
date for each of them, and etc...???
maybe it's possible, but i can't see how !
so please help !
An Dong

 

[Sue Mosher [MVP-Outlook]]

General rule of thumb: Use formulas for simple calculations and VBScript
code behind the form for more complex ones. Your calculation clearly falls
into the latter category.

At http://www.outlookcode.com/codedetail.aspx?id=153, you'll find a
NextBusinessDay() function that works like DateAdd(), only it adds only
weekdays.

I don't have a good algorithm handy for determining the number of weekends
between two dates, but with a little experimentation, it should be possible
for you to come up with one by examining the day of the week for the start
date (use the Weekday() function) and the number of days between the start
and due dates (use the DateDiff() function).
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Hello,
Yes, sure, but how can you write a formula saying : i want you to add
two days to the due date for each week-end between the start date and
the due date, and if because of this operation one or more week end is
added between the start date and the due date, add two days to the due
date for each of them, and etc...???
maybe it's possible, but i can't see how !
so please help !
An Dong

"Sue Mosher [MVP-Outlook]" <[email protected]> wrote in message

Why do you say that calculating the due date in a formula is impossible?
That's exactly what the DateAdd() function is designed for.

 

[An Dong]

thank you for your help, but i already wrote the function i need. now
i wonder how i could call my function from the form ! where do i have
to save it ? with which extension ? is it possible just to make the
function accessible by outlook and call it in the formula field or is
it more complicated ?
i join my function again (this time in english) if it can help.

Function GetDateDeFin(dteStart As Date, intNbrBusinessDays As Integer)
As Date
Dim dteTempDate As Date
Dim intNbrWeeks As Integer
Dim intNbrDaysLeft As Integer
Dim i As Integer
Dim test As Boolean
Dim dteTest As Date

' number of weeks to add to the start date (deduced
' from the number of working days as parameter) :
intNbrWeeks = intNbrBusinessDays / 5

' add the weeks to the start date :
dteTempDate = DateAdd("w", intNbrWeeks, dteStart)

' number of business days left after we add
' the complete weeks :
intNbrDaysLeft = intNbrBusinessDays
Do While intNbrDaysLeft >= 5
intNbrDaysLeft = intNbrDaysLeft - 5
Loop

'test if one of the intNbrDaysLeft days to add is a saturday :
i = 1
test = false
dteTest = dteTempDate
Do While i <= intNbrDaysLeft and test = false
If Weekday(dteTest) = Saturday Then test = true
End If
i = i+1
dteTest = DateAdd("d", 1, dteTest)
Loop

' add the last week-end if there is a last week-end to add
If test = true Then dteTempDate = DateAdd("d", 2, dteTempDate)

' add the days left
dteTempDate = DateAdd("d", intNbrDaysLeft, dteTempDate)

GetDateDeFin = dteTempDate
End Function

 

[Sue Mosher [MVP-Outlook]]

First you need to convert the function to VBScript by removing the data
typing from the variable and procedure declarations. Then, paste it into
your form's code and call it from the other VBScript procedure(s) in your
form, i.e. not from a formula field.

 

[An Dong]

hello,
i converted my function to VBScript, i pasted it into my form's
code,but i don't understand what you mean : "call it from the other
VBScript procedure(s) in your form". what should i exactly do ?
thank you, i can feel i'm getting near !
An Dong

 

[Sue Mosher [MVP-Outlook]]

Sorry, but since you've omitted information from earlier messages in this
conversation, I don't know what this is about.

 

[An Dong]

here are the previous informations. thank you.
An Dong

you :
Sorry, but since you've omitted information from earlier messages in this
conversation, I don't know what this is about.

me :
hello,
i converted my function to VBScript, i pasted it into my form's
code,but i don't understand what you mean : "call it from the other
VBScript procedure(s) in your form". what should i exactly do ?
thank you, i can feel i'm getting near !
An Dong

you :
First you need to convert the function to VBScript by removing the data
typing from the variable and procedure declarations. Then, paste it into
your form's code and call it from the other VBScript procedure(s) in your
form, i.e. not from a formula field.

me :
thank you for your help, but i already wrote the function i need. now
i wonder how i could call my function from the form. where do i have
to save it ? with which extension ? is it possible just to make the
function accessible by outlook and call it in the formula field or is
it more complicated ?

you :
General rule of thumb: Use formulas for simple calculations and VBScript
code behind the form for more complex ones. Your calculation clearly falls
into the latter category.

At http://www.outlookcode.com/codedetail.aspx?id=153, you'll find a
NextBusinessDay() function that works like DateAdd(), only it adds only
weekdays.

I don't have a good algorithm handy for determining the number of weekends
between two dates, but with a little experimentation, it should be possible
for you to come up with one by examining the day of the week for the start
date (use the Weekday() function) and the number of days between the start
and due dates (use the DateDiff() function).

me :
Hello,
Yes, sure, but how can you write a formula saying : i want you to add
two days to the due date for each week-end between the start date and
the due date, and if because of this operation one or more week end is
added between the start date and the due date, add two days to the due
date for each of them, and etc...???
maybe it's possible, but i can't see how !
so please help !
An Dong

you :
Why do you say that calculating the due date in a formula is impossible?
That's exactly what the DateAdd() function is designed for.

Hello,
under outlook 2000, i'm creating a customised form based on a task
form. i want a field to be automatically updated when an other one is
filled. and i want this field to display the due date from a
predifined number of business days. but calculating the due date in a
forumla is impossible. so i wrote a function and now i wonder how
outlook could have access to it so that i could just enter my function
and the parameters as the forumla. i hope my english is good enough
for you to understand...
if it can help, i join the function i wrote.
Thank you !
An Dong

 

[Sue Mosher [MVP-Outlook]]

Since you want one field to change in response to another's change, you'll
use the CustomPropertyChange event. See
http://www.outlookcode.com/d/propsyntax.htm#custom

You call a function by setting a variable or object property to the value
returned by the variable.

Put those two concepts together with the basic syntax for working with
custom properties in Outlook and you'll have something like:

Sub Item_CustomPropertyChange(ByVal Name)
Select Case Name
Case "MyProp1"
Item.UserProperties("MyProp2") = MyFunction(args)
Case "MyProp2"
' code to handle a change in MyProp2 goes here

' continue with Case statements for other properties
' whose values you want to monitor
End Select
End Sub

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[An Dong]

Hello,
Thank you very much, it's OK !
An Dong

Since you want one field to change in response to another's change, you'll
use the CustomPropertyChange event. See
http://www.outlookcode.com/d/propsyntax.htm#custom

You call a function by setting a variable or object property to the value
returned by the variable.

Put those two concepts together with the basic syntax for working with
custom properties in Outlook and you'll have something like:

Sub Item_CustomPropertyChange(ByVal Name)
Select Case Name
Case "MyProp1"
Item.UserProperties("MyProp2") = MyFunction(args)
Case "MyProp2"
' code to handle a change in MyProp2 goes here

' continue with Case statements for other properties
' whose values you want to monitor
End Select
End Sub

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers