Can't validate dates in Outlook form

[Guest]

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Sue Mosher [MVP-Outlook]]

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

 

[Guest]

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

Thanks a bunch.

--
I'm complicated.

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

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

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Sue Mosher [MVP-Outlook]]

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

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

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

Thanks a bunch.

--
I'm complicated.

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

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

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Guest]

That's what I thought, too. Hence, my problem! Is there some setting I'm
missing? I've done so much googling, my google organ hurts.

Thanks.
--
I'm complicated.

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

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

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

Thanks a bunch.

--
I'm complicated.

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

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



Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Sue Mosher [MVP-Outlook]]

No, there are no settings related to form formulas.

You still didn't say what kind of form it is -- message, post, appointment, etc.?

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

That's what I thought, too. Hence, my problem! Is there some setting I'm
missing? I've done so much googling, my google organ hurts.

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

:

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Guest]

oops, sorry, it's an IPM.Note. Does that sound right? (Sorry; not sure if
that's what you want)


--
I'm complicated.

No, there are no settings related to form formulas.

You still didn't say what kind of form it is -- message, post, appointment, etc.?

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

That's what I thought, too. Hence, my problem! Is there some setting I'm
missing? I've done so much googling, my google organ hurts.

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

:

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Sue Mosher [MVP-Outlook]]

That will do. It means it's a message form, so you can use VBScript code in the Item_Send event handler to perform any validation you want to do:

Function Item_Send()
If <your criteria are not met> Then
Item_Send = False
MsgBox "You did something wrong"
End If
End Function

The syntax for working with Outlook properties in VBScript is different from that in formulas. See http://www.outlookcode.com/d/propsyntax.htm

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

oops, sorry, it's an IPM.Note. Does that sound right? (Sorry; not sure if
that's what you want)


--
I'm complicated.

No, there are no settings related to form formulas.

You still didn't say what kind of form it is -- message, post, appointment, etc.?

That's what I thought, too. Hence, my problem! Is there some setting I'm
missing? I've done so much googling, my google organ hurts.

:

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?

:

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?

Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer

 

[Guest]

Yes, that worked perfectly. Thanks so much!

(Though, I still wonder why it wouldn't let me use AND or OR...)

Spencer
--
I'm complicated.

That will do. It means it's a message form, so you can use VBScript code in the Item_Send event handler to perform any validation you want to do:

Function Item_Send()
If <your criteria are not met> Then
Item_Send = False
MsgBox "You did something wrong"
End If
End Function

The syntax for working with Outlook properties in VBScript is different from that in formulas. See http://www.outlookcode.com/d/propsyntax.htm

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

oops, sorry, it's an IPM.Note. Does that sound right? (Sorry; not sure if
that's what you want)


--
I'm complicated.

No, there are no settings related to form formulas.

You still didn't say what kind of form it is -- message, post, appointment, etc.?

That's what I thought, too. Hence, my problem! Is there some setting I'm
missing? I've done so much googling, my google organ hurts.

:

AND and OR statements *are* allowed in formulas.

Is it a message form? If so, you can use code in the Item_Send event handler.

It's not the logic that's in question (though, that might be wrong, too),
it's that it appears I am not allowed to use AND or OR statements.

I'm not sure what type of form it is. IPM.Note? HTML? Are one of those what
you mean?

How could I make it VBScript code and incorporate it into the form to
validate the field?


:

IMO, that's too complicated a validation formula. You'd be better off building in with VBScript code. What kind of form are you using?


Hi, I've been trying to create a formula for validation and it hasn't been
working (hence, my post). The form is for requesting loaner laptops, and
since it takes time to prepare a laptop for a user, I need to validate the
entry in the DateRequired field.

The idea is that 2 business days of warning is required. Therefore, if the
DateRequired ends up on a Monday or Tuesday, it makes sure that the user has
requested it at least 4 days prior (to account for Sat and Sun). Otherwise,
if the DateRequired ends up on Wed, Thurs, or Fri, it just makes sure it was
requested at least 2 days prior. If the DateRequired ends up on Sat or Sun,
I want to create a separate error message, but that's a separate question
that I'll ask later.

In the meantime, this code that I've been pasting comes back with an invalid
formula error, and I can't see why. [DateRequired] is the name of the very
same entry field I'm trying to validate (I'm pretty sure that's perfectly
OK). I've double checked the parentheses, I've used the functions from the
function query button, etc, etc. Can someone please tell me?

((Weekday( [DateRequired] ) = 2 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) = 3 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 4))
Or
((Weekday( [DateRequired] ) <> 1 ) And
(Weekday( [DateRequired] ) <> 2 ) And
(Weekday( [DateRequired] ) <> 3 ) And
(Weekday( [DateRequired] ) <> 7 ) And
((DateValue( [DateRequired] ) - DateValue( Now() ) ) >= 2))


The OTHER question I have, if you've got the time, is: is it possible to
MsgBox more than one error message from the same field, depending on the type
of error (ie. You can't request a laptop on the weekend. VS. Laptop requests
must be made at least 2 business days in advance.)?

I guess this is just because I don't quite understand the format of the
validation function. I think it's a Visual Basic for Applications formatted
boolean statement, is it not?

Thanks for reading this far!!!!
Spencer