access expression using two "if" statements

[Guest]

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH

 

[Guest]

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

Steve,

Should [Today + 3 Days] be in the expression?

CJM

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

One of the sentences in my original post contains an error. "No" should be
"Yes" in the sentence: When "No" is entered into the third field, the value
in the fourth field does change to the value for [Today + 28 Days].

 

[Guest]

Yes. My proofreader isn't working today


OK, one more time:

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 3 Days]))


One suggestion... don't use spaces or special characters in object names
(forms, queries, fields,reports,etc). If you want to seperate words, use the
underscore -
[PatientOver1YearOld] or [Patient_Over_1_Year_Old]

No one should see the names but you (the developer), so using spaces just
makes it harder in the long run. Look for "naming conventions" in "The Access
Web" site.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

Should [Today + 3 Days] be in the expression?

CJM

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

#Name? appears in [Expiration Date of Irradiated Unit] field using your
expression. I also tried "Yes" instead of True (See my original expression).
CJM

Yes. My proofreader isn't working today


OK, one more time:

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 3 Days]))


One suggestion... don't use spaces or special characters in object names
(forms, queries, fields,reports,etc). If you want to seperate words, use the
underscore -
[PatientOver1YearOld] or [Patient_Over_1_Year_Old]

No one should see the names but you (the developer), so using spaces just
makes it harder in the long run. Look for "naming conventions" in "The Access
Web" site.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

Should [Today + 3 Days] be in the expression?

CJM

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

SteveS

I followed your advice and cleaned up my field names. I tried your
expression again and it worked! Thank you so much for the good advice and
the expression.

CJM

#Name? appears in [Expiration Date of Irradiated Unit] field using your
expression. I also tried "Yes" instead of True (See my original expression).
CJM

Yes. My proofreader isn't working today


OK, one more time:

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 3 Days]))


One suggestion... don't use spaces or special characters in object names
(forms, queries, fields,reports,etc). If you want to seperate words, use the
underscore -
[PatientOver1YearOld] or [Patient_Over_1_Year_Old]

No one should see the names but you (the developer), so using spaces just
makes it harder in the long run. Look for "naming conventions" in "The Access
Web" site.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

Should [Today + 3 Days] be in the expression?

CJM


:

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.

 

[Guest]

Wonderful!

Glad you got it to work.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS

I followed your advice and cleaned up my field names. I tried your
expression again and it worked! Thank you so much for the good advice and
the expression.

CJM

#Name? appears in [Expiration Date of Irradiated Unit] field using your
expression. I also tried "Yes" instead of True (See my original expression).
CJM

Yes. My proofreader isn't working today


OK, one more time:

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 3 Days]))


One suggestion... don't use spaces or special characters in object names
(forms, queries, fields,reports,etc). If you want to seperate words, use the
underscore -
[PatientOver1YearOld] or [Patient_Over_1_Year_Old]

No one should see the names but you (the developer), so using spaces just
makes it harder in the long run. Look for "naming conventions" in "The Access
Web" site.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Steve,

Should [Today + 3 Days] be in the expression?

CJM


:

Sorry, I was testing the formula and forgot to change the reference

Try: (watch for line wrap)

=IIf(IsNull([Patient Over 1 Year Old?]),Null,IIf([Patient Over 1 Year
Old?]=True,[Today + 28 Days],[Today + 28 Days]))


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

CJM,

Try

=IIf(IsNull([Text7]),Null,IIf([text7]=True,[Today + 28 Days],[Today + 28
Days]))


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I am building a form that has the following fields among others:

1) Today + 28 Days
2) Today + 3 Days
3) Patient Over 1 Year Old?
4) Expiration Date of Irradiated Unit

Short date is the specified format for each of the date fields. The third
field is a text field with a Yes/No format.

I was able to use expressions to automatically populate the first two
fields. I want the fourth field to automatically populate with either the
value in the first or second field depending on the value in the third field.
If the value in the third field is "yes", the value from the first field
should fill in the fourth field. If the value in the third field is "no",
the value from the second field should fill in the fourth field. I tried the
following expression in the control source for the last field:

=Iff ([Patient Over 1 Year Old?]="Yes", [Today + 28 Days], [Today + 3 Days])

The expression automatically defaults the value for [Today + 3 Days] in the
fourth field before the third field is answered because "Yes" is not in the
third field when the form is opened or a new record is started. When "No" is
entered into the third field, the value in the fourth field does change to
the value for [Today + 28 Days]. Ideally, I would like the fourth field to
remain blank until the third field is answered. Is there another expression
that would accomplish this. Thank you.