if or and expression

[RA@UIC]

Hi All,
I'm still learning the ropes of Access as a relatively new user. Could
someone help me with writing an expression for an "if or and" function in
conjunction with a calculating a future date function?

E.g., If week 1 displays null, then enter this date in the future using this
formula. If week 1 has a date entered, then apply formula towards
it...perhaps this is sort of vague, and I apologize. This is what I have so
far, but can't get it to work:

=IIf(IsNull([Date 2nd Pre-Intervention Scheduled]), DateSerial(Year([Date
Pre-Intervention Scheduled]),Month([Date Pre-Intervention
Scheduled]),Day([Date Pre-Intervention Scheduled])+42)

Many thanks in advance

 

[Klatuu]

That will not work at all. You are trying to do a DateSerial function on a
Null value. And, the DateSerial will not return what you think it will and
is not really the correct function for this.

What do you mean by "If week 1 displays null, then enter this date in the
future"?
What is "this date".

If your field is Null and you want to replace it with a date 42 days from
the current date, I would do it this way:

=Nz([Date 2nd Pre-Intervention Scheduled], DateAdd("d", 42, [Date 2nd
Pre-Intervention Scheduled]))

 

[RA@UIC]

This function would concern 3 fields. "Pre-assessment 1 Date - Immediate
starts", "Pre-assessment 2 Date - Control Starts", and "Date of Week 7
Post-Assessments".

The function I was attempting to create would do the following:

If the "Pre-assessment 2 Date - Control Starts" field DOES NOT have a date
entered in, then "Date of Week 7 Post-Assessments" would equal a date 7 weeks
later from the date listed in "Pre-assessment 1 Date - Immediate starts".

Or

If "Pre-assessment 2 Date - Control Starts" DOES have a date, I would like
"Date of Week 7 Post-Assessments" to equal 7 weeks later from the date listed
in "Pre-assessment 2 Date - Control Starts".

I'm assuming this function would be embedded within the "Date of Week 7
Post-Assessments" field

Thanks for your response, and again, for any insight you can offer.

Many thanks,
Dan

That will not work at all. You are trying to do a DateSerial function on a
Null value. And, the DateSerial will not return what you think it will and
is not really the correct function for this.

What do you mean by "If week 1 displays null, then enter this date in the
future"?
What is "this date".

If your field is Null and you want to replace it with a date 42 days from
the current date, I would do it this way:

=Nz([Date 2nd Pre-Intervention Scheduled], DateAdd("d", 42, [Date 2nd
Pre-Intervention Scheduled]))



--
Dave Hargis, Microsoft Access MVP

Hi All,
I'm still learning the ropes of Access as a relatively new user. Could
someone help me with writing an expression for an "if or and" function in
conjunction with a calculating a future date function?

E.g., If week 1 displays null, then enter this date in the future using this
formula. If week 1 has a date entered, then apply formula towards
it...perhaps this is sort of vague, and I apologize. This is what I have so
far, but can't get it to work:

=IIf(IsNull([Date 2nd Pre-Intervention Scheduled]), DateSerial(Year([Date
Pre-Intervention Scheduled]),Month([Date Pre-Intervention
Scheduled]),Day([Date Pre-Intervention Scheduled])+42)

Many thanks in advance

 

[RA@UIC]

This function would concern 3 fields: "Pre-assessment 1 Date - Immediate
starts", "Pre-assessment 2 Date - Control Starts", and "Date of Week 7
Post-Assessments"

The function I was attempting to create would do the following:

If the "Pre-assessment 2 Date - Control Starts" field DOES NOT have a date
entered in, then "Date of Week 7 Post-Assessments" would equal a date 7 weeks
later from the date listed in "Pre-assessment 1 Date - Immediate starts".

Or

If "Pre-assessment 2 Date - Control Starts" DOES have a date, I would like
"Date of Week 7 Post-Assessments" to equal 7 weeks later from the date listed
in "Pre-assessment 2 Date - Control Starts".

I'm assuming this function would be embedded within the "Date of Week 7
Post-Assessments" field

Thanks for your response, and again, for any insight you can offer.

Many thanks,
Dan

That will not work at all. You are trying to do a DateSerial function on a
Null value. And, the DateSerial will not return what you think it will and
is not really the correct function for this.

What do you mean by "If week 1 displays null, then enter this date in the
future"?
What is "this date".

If your field is Null and you want to replace it with a date 42 days from
the current date, I would do it this way:

=Nz([Date 2nd Pre-Intervention Scheduled], DateAdd("d", 42, [Date 2nd
Pre-Intervention Scheduled]))



--
Dave Hargis, Microsoft Access MVP

Hi All,
I'm still learning the ropes of Access as a relatively new user. Could
someone help me with writing an expression for an "if or and" function in
conjunction with a calculating a future date function?

E.g., If week 1 displays null, then enter this date in the future using this
formula. If week 1 has a date entered, then apply formula towards
it...perhaps this is sort of vague, and I apologize. This is what I have so
far, but can't get it to work:

=IIf(IsNull([Date 2nd Pre-Intervention Scheduled]), DateSerial(Year([Date
Pre-Intervention Scheduled]),Month([Date Pre-Intervention
Scheduled]),Day([Date Pre-Intervention Scheduled])+42)

Many thanks in advance

 

[Klatuu]

DateAdd("ww",7, Nz([Pre-assessment 2 Date - Control Starts], [Pre-assessment
1 Date - Immediate starts]))

--
Dave Hargis, Microsoft Access MVP

This function would concern 3 fields: "Pre-assessment 1 Date - Immediate
starts", "Pre-assessment 2 Date - Control Starts", and "Date of Week 7
Post-Assessments"

The function I was attempting to create would do the following:

If the "Pre-assessment 2 Date - Control Starts" field DOES NOT have a date
entered in, then "Date of Week 7 Post-Assessments" would equal a date 7 weeks
later from the date listed in "Pre-assessment 1 Date - Immediate starts".

Or

If "Pre-assessment 2 Date - Control Starts" DOES have a date, I would like
"Date of Week 7 Post-Assessments" to equal 7 weeks later from the date listed
in "Pre-assessment 2 Date - Control Starts".

I'm assuming this function would be embedded within the "Date of Week 7
Post-Assessments" field

Thanks for your response, and again, for any insight you can offer.

Many thanks,
Dan

That will not work at all. You are trying to do a DateSerial function on a
Null value. And, the DateSerial will not return what you think it will and
is not really the correct function for this.

What do you mean by "If week 1 displays null, then enter this date in the
future"?
What is "this date".

If your field is Null and you want to replace it with a date 42 days from
the current date, I would do it this way:

=Nz([Date 2nd Pre-Intervention Scheduled], DateAdd("d", 42, [Date 2nd
Pre-Intervention Scheduled]))



--
Dave Hargis, Microsoft Access MVP

Hi All,
I'm still learning the ropes of Access as a relatively new user. Could
someone help me with writing an expression for an "if or and" function in
conjunction with a calculating a future date function?

E.g., If week 1 displays null, then enter this date in the future using this
formula. If week 1 has a date entered, then apply formula towards
it...perhaps this is sort of vague, and I apologize. This is what I have so
far, but can't get it to work:

=IIf(IsNull([Date 2nd Pre-Intervention Scheduled]), DateSerial(Year([Date
Pre-Intervention Scheduled]),Month([Date Pre-Intervention
Scheduled]),Day([Date Pre-Intervention Scheduled])+42)

Many thanks in advance

 

[RA@UIC]

Thank you so much!

DateAdd("ww",7, Nz([Pre-assessment 2 Date - Control Starts], [Pre-assessment
1 Date - Immediate starts]))

--
Dave Hargis, Microsoft Access MVP

This function would concern 3 fields: "Pre-assessment 1 Date - Immediate
starts", "Pre-assessment 2 Date - Control Starts", and "Date of Week 7
Post-Assessments"

The function I was attempting to create would do the following:

If the "Pre-assessment 2 Date - Control Starts" field DOES NOT have a date
entered in, then "Date of Week 7 Post-Assessments" would equal a date 7 weeks
later from the date listed in "Pre-assessment 1 Date - Immediate starts".

Or

If "Pre-assessment 2 Date - Control Starts" DOES have a date, I would like
"Date of Week 7 Post-Assessments" to equal 7 weeks later from the date listed
in "Pre-assessment 2 Date - Control Starts".

I'm assuming this function would be embedded within the "Date of Week 7
Post-Assessments" field

Thanks for your response, and again, for any insight you can offer.

Many thanks,
Dan

That will not work at all. You are trying to do a DateSerial function on a
Null value. And, the DateSerial will not return what you think it will and
is not really the correct function for this.

What do you mean by "If week 1 displays null, then enter this date in the
future"?
What is "this date".

If your field is Null and you want to replace it with a date 42 days from
the current date, I would do it this way:

=Nz([Date 2nd Pre-Intervention Scheduled], DateAdd("d", 42, [Date 2nd
Pre-Intervention Scheduled]))



--
Dave Hargis, Microsoft Access MVP


:

Hi All,
I'm still learning the ropes of Access as a relatively new user. Could
someone help me with writing an expression for an "if or and" function in
conjunction with a calculating a future date function?

E.g., If week 1 displays null, then enter this date in the future using this
formula. If week 1 has a date entered, then apply formula towards
it...perhaps this is sort of vague, and I apologize. This is what I have so
far, but can't get it to work:

=IIf(IsNull([Date 2nd Pre-Intervention Scheduled]), DateSerial(Year([Date
Pre-Intervention Scheduled]),Month([Date Pre-Intervention
Scheduled]),Day([Date Pre-Intervention Scheduled])+42)

Many thanks in advance