trying to define a date field from an other one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

An Dong - 26 Jul 2004 09:32 GMT

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

Reply to this Message

Sue Mosher [MVP-Outlook] - 26 Jul 2004 12:54 GMT

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

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> Hello,
> under outlook 2000, i'm creating a customised form based on a task
[quoted text clipped - 55 lines]
> GetDateDeFin = dteDateVariable
> End Function

Reply to this Message

An Dong - 27 Jul 2004 08:28 GMT

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

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

Reply to this Message

Sue Mosher [MVP-Outlook] - 27 Jul 2004 12:31 GMT

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).

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> Hello,
> Yes, sure, but how can you write a formula saying : i want you to add
[quoted text clipped - 8 lines]
> > Why do you say that calculating the due date in a formula is impossible?
> > That's exactly what the DateAdd() function is designed for.

Reply to this Message

An Dong - 28 Jul 2004 09:14 GMT

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

Reply to this Message

Sue Mosher [MVP-Outlook] - 28 Jul 2004 13:23 GMT

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.

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> 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
[quoted text clipped - 45 lines]
> GetDateDeFin = dteTempDate
> End Function

Reply to this Message

An Dong - 29 Jul 2004 15:25 GMT

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

Reply to this Message

Sue Mosher [MVP-Outlook] - 29 Jul 2004 15:41 GMT

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

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> 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

Reply to this Message

An Dong - 30 Jul 2004 12:12 GMT

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

Reply to this Message

Sue Mosher [MVP-Outlook] - 30 Jul 2004 13:30 GMT

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

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> here are the previous informations. thank you.
> An Dong
[quoted text clipped - 66 lines]
> Thank you !
> An Dong

Reply to this Message

An Dong - 04 Aug 2004 09:46 GMT

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
[quoted text clipped - 89 lines]
> > Thank you !
> > An Dong

Reply to this Message