Help with DateAdd function please!

[surreal1780]

Hello all,
I am trying to construct a DateAdd function, but am having difficulty. The function involves 3 fields:

Pre-assessment 1 Date - Immediate starts
Pre-assessment 2 Date - Control Starts
Date of 7 Week Follow-up post-intervention

I am trying to create a function for the "Date of 7..." field so that it generates a date 7 weeks (49 days) from "Pre-assessment 1..." if there is no date entered in "Pre-assessment 2..." (i.e., Pre-assessment 2..." field is null)

OR

if both "Pre-assessment 1..." and "Pre-assessment 2..." both have dates, the "Date of 7..." field will generate a date 7 weeks (49 days) from the date entered in "Pre-assessment 2..." field


This is what I've tried so far in the "Date of 7..." field, and it does not seem to work:

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

and I've tried:

=DateAdd("d",49, Nz([Pre-assessment 2 Date - Control Starts], [Pre-assessment 1 Date - Immediate starts]))


Any help would be most appreciated please!

Many thanks in advance,
Dan

 

[pbaldy]

I just did a brief test and that worked as expected. What is going wrong for you?

 

[CraigDolphin]

I am trying to create a function for the "Date of 7..." field so that it generates a date 7 weeks (49 days) from "Pre-assessment 1..." if there is no date entered in "Pre-assessment 2..." (i.e., Pre-assessment 2..." field is null)

OR

if both "Pre-assessment 1..." and "Pre-assessment 2..." both have dates, the "Date of 7..." field will generate a date 7 weeks (49 days) from the date entered in "Pre-assessment 2..." field

This should do it:

=Switch(Not Isnull([Pre-assessment 1 Date - Immediate starts] AND Isnull([Pre-assessment 2 Date - Control Starts]),DateAdd("d",49,[Pre-assessment 1 Date - Immediate starts]),Not Isnull([Pre-assessment 1 Date - Immediate starts] AND Not Isnull([Pre-assessment 2 Date - Control Starts]),DateAdd("d",49,[Pre-assessment 2 Date - Control Starts],Isnull([Pre-assessment 1 Date - Immediate starts] AND Isnull([Pre-assessment 2 Date - Control Starts]),Null)

And consider yourself slapped with a wet herring for using a field naming 'convention' that allows you to use spaces in such super-long field names For the record, it's bad practice and should be avoided.

 

[surreal1780]

As you can see from the picture, when I enter in a date in "Pre-assessment 1..." no date is generated in "Date of 7". However, when I enter in a date in "Pre-assessment 2..." the function works. Ideas?

 

[surreal1780]

CraigDolphin: I received the following error when I tried inserting the function:

"the expression you entered has a function containing the wrong number of arguments"

??? no idea what that means...sorry, I acknowledge I am still a novice in many respects using Access.

 

[pbaldy]

Can you post a sample db?

 

[CraigDolphin]

Hmm...looks like I dropped a couple of closing brackets for the isnull tests.

Try this:

=Switch(Not Isnull([Pre-assessment 1 Date - Immediate starts]) AND Isnull([Pre-assessment 2 Date - Control Starts]),DateAdd("d",49,[Pre-assessment 1 Date - Immediate starts]),Not Isnull([Pre-assessment 1 Date - Immediate starts]) AND Not Isnull([Pre-assessment 2 Date - Control Starts]),DateAdd("d",49,[Pre-assessment 2 Date - Control Starts]),Isnull([Pre-assessment 1 Date - Immediate starts]) AND Isnull([Pre-assessment 2 Date - Control Starts]),Null)

It would be a lot easier to debug if you'd named these fields something a bit shorter and to the point like 'PreDate1' and 'PreDate2' or soemthing

 

[surreal1780]

thank you both!

Thank you both for your help. I'll keep the shortening of names in mind--I know it's cumbersome so I'll definitely avoid using them in the future.

thanks,
D.