Validation formula in a form I created.

[Guest]

Greetings,
On my form there are two fields:
1. a combo box where the user selects one of three choices, LOW, MEDIUM, or
HIGH-QPRIORITY.
2. a Text Box where a date is entered - QDNDATE.

I would like to validate the text box to sayif the date entered is less than
10 days in the future ([QDNDATE] < Date()+10) and the QPRIORITY box is HIGH
then the date is okay as it stands. If the Date is not more than 10 days in
the future and the QPRIORITY box is not HIGH, then an error message to the
user adn DO NOT SEND until corrected.

QDNDATE: more than 10 days in the future, no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is HIGH,
no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is not
HIGH, then user must change the date.

In the Properties box under Validation in theformula area I have
[QDNDATE]>Date()+10 to ensure date is more than ten days from current date.

Question: Can I add more to the formulat to include another field on the
form and how? EX: [QDNDATE]<Date()+10 AND QPRIORITY <> "HIGH" this does not
work for me. Not sure how I 'connect' two formulas in the same box.

Any and all help appreciated.

Thanks,

Yuda

 

[Sue Mosher [MVP-Outlook]]

A validation formula must return True if the data is "good" and False if the data is "bad." The first step therefore is to construct expressions that capture all the cases in which the data can be said to be good. You've outlined those verbally, so all that remains should be to express them in the formula notation:

QDNDATE: more than 10 days in the future, no further validation required

[QDNDATE] > Date() + 10

You could also use the DateDiff() function for this expression.

QDNDATE: not more than 10 days in the future and the QPRIORITY box is HIGH,
no further validation required

([QDNDate] <= Date() + 10) AND ([QPRIORITY] = "HIGH")

The final step is to join these two expressions with OR, because the data is "good" when either expression returns True:

([QDNDATE] > Date() + 10) OR (([QDNDATE] <= Date() + 10) AND ([QPRIORITY] = "HIGH"))

That should be the formula that works for the QDNDATE field.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

Greetings,
On my form there are two fields:
1. a combo box where the user selects one of three choices, LOW, MEDIUM, or
HIGH-QPRIORITY.
2. a Text Box where a date is entered - QDNDATE.

I would like to validate the text box to sayif the date entered is less than
10 days in the future ([QDNDATE] < Date()+10) and the QPRIORITY box is HIGH
then the date is okay as it stands. If the Date is not more than 10 days in
the future and the QPRIORITY box is not HIGH, then an error message to the
user adn DO NOT SEND until corrected.

QDNDATE: more than 10 days in the future, no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is HIGH,
no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is not
HIGH, then user must change the date.

In the Properties box under Validation in theformula area I have
[QDNDATE]>Date()+10 to ensure date is more than ten days from current date.

Question: Can I add more to the formulat to include another field on the
form and how? EX: [QDNDATE]<Date()+10 AND QPRIORITY <> "HIGH" this does not
work for me. Not sure how I 'connect' two formulas in the same box.

Any and all help appreciated.

Thanks,

Yuda

 

[Guest]

Sue,

This is great and it works.

Thanks,

Yuda

A validation formula must return True if the data is "good" and False if the data is "bad." The first step therefore is to construct expressions that capture all the cases in which the data can be said to be good. You've outlined those verbally, so all that remains should be to express them in the formula notation:

QDNDATE: more than 10 days in the future, no further validation required

[QDNDATE] > Date() + 10

You could also use the DateDiff() function for this expression.

QDNDATE: not more than 10 days in the future and the QPRIORITY box is HIGH,
no further validation required

([QDNDate] <= Date() + 10) AND ([QPRIORITY] = "HIGH")

The final step is to join these two expressions with OR, because the data is "good" when either expression returns True:

([QDNDATE] > Date() + 10) OR (([QDNDATE] <= Date() + 10) AND ([QPRIORITY] = "HIGH"))

That should be the formula that works for the QDNDATE field.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

Greetings,
On my form there are two fields:
1. a combo box where the user selects one of three choices, LOW, MEDIUM, or
HIGH-QPRIORITY.
2. a Text Box where a date is entered - QDNDATE.

I would like to validate the text box to sayif the date entered is less than
10 days in the future ([QDNDATE] < Date()+10) and the QPRIORITY box is HIGH
then the date is okay as it stands. If the Date is not more than 10 days in
the future and the QPRIORITY box is not HIGH, then an error message to the
user adn DO NOT SEND until corrected.

QDNDATE: more than 10 days in the future, no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is HIGH,
no further validation required
QDNDATE: not more than 10 days in the future and the QPRIORITY box is not
HIGH, then user must change the date.

In the Properties box under Validation in theformula area I have
[QDNDATE]>Date()+10 to ensure date is more than ten days from current date.

Question: Can I add more to the formulat to include another field on the
form and how? EX: [QDNDATE]<Date()+10 AND QPRIORITY <> "HIGH" this does not
work for me. Not sure how I 'connect' two formulas in the same box.

Any and all help appreciated.

Thanks,

Yuda