DATEDD FUNCTION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If the following conditions can exist for the control LastDayAppealStepB. If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date for the
control LastDayAppealStepB except when the 3 conditions above exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))). I got an
error message that this expression contains the wrong number of arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])" is
causing the problem. I've tried everything I know to get it to work. So can
someone break
down this expression into segments and explain what each segment is trying
to do.

Thanks for the help.
 
Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
 
Hi Bob,

I've only had a quick glance at this, and not gone through all the logic, so
there may be other problems. However, the standout one is that your DateAdd
expression in the False clause of the Iif statement has too many arguments,
thus:
"d"
+7
[StepADecisionDate]
nz([LastDayAppealStepB],[StepADecisionDate])

The DateAdd function only takes three arguments.

There's no explanation of what the +7 is meant to be doing.

If you are trying to add 7 days to a date, and need to add it a different
date depending on whether the date is null, you'll need two DateAdd
statements in your Iif statement - one in each of the true and false return
parameters. Something like:
=IIf(IsNull([StepADecisionDate]),DateAdd("d",+7,[LastDayAppealStepB]),DateAdd("d",+7,[StepADecisionDate]))

This will return a date 7 days after LastDayAppealStepB if StepADecisionDate
is null, or a date 7 days after StepADecisionDate is it is not null (and
null if LastDayAppealStepB is null - if you need to prevent that, you'll
need another complete Iif statement in the true clause of this one).
Substitute other field names if that not exactly what you want.

HTH,

Rob


"(e-mail address removed).(donotspam)"
 
Thanks Graham and Rob for your responses. I used Graham's expression. I have
part of what I need. My problem still is that I get a value for the unbound
control LastDayAppealStepBP3 even if the bound control Disposition has a
value of "ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA"

I have asked previoulsy how to create a conditional IF compilation that
executes block of code and where would I put that code for an unbound
control. That is, I have created the code below for the Before Update event
procedure for the unbound control LastDayAppealStepBP3 and the control source
is
=IIf(IsNull([StepADecisionDateP3]),Null,DateAdd("d",+7,[StepADecisionDateP3])).
The Before Update event procedure for the unbound control
LastDayAppealStepBP3 has no effect when the control Disposition conditions
"ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA" exist. The
control source is controlling what values are displayed. If I delete the
control source there is no value in unbound control LastDayAppealStepBP3.

Private Sub LastDayAppealStepBP3_BeforeUpdate(Cancel As Integer)

'If the bound control DispositionP3 has a value that equals
"ResolvedInformalStepA" then
If (DispositionP4) = "ResolvedInformalStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals "ResolvedStepA"
then
ElseIf (DispositionP4) = "ResolvedStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals "WithdrawnStepA"
then
ElseIf (DispositionP4) = "WithdrawnStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Else the unbound control LastDayAppealStepBP3 should perform the expression
= IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3]))
Else
LastDayAppealStepBP3 = IIf(IsNull([StepADecisionDateP3]), Null, DateAdd("d",
7, [StepADecisionDateP3]))
End If
End Sub

Thanks
--
Bob


Graham Mandeno said:
Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
If the following conditions can exist for the control LastDayAppealStepB.
If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date for the
control LastDayAppealStepB except when the 3 conditions above exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))).
I got an
error message that this expression contains the wrong number of arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])" is
causing the problem. I've tried everything I know to get it to work. So
can
someone break
down this expression into segments and explain what each segment is trying
to do.

Thanks for the help.
Click to expand...
Click to expand...
 
Hi Bob,

You don't need any code to do this; all you need is the correct expression
in the unbound control LastDayAppealStepBP3. Since the value you want is
dependent on two different bound controls, you will need (at least) two Iif
statements in the expression in your unbound control, nested one within the
other.

Try this:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] =
"WithdrawnStepA"),Null,IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d",
7, [StepADecisionDateP3])))

HTH,

Rob

PS. If DispositionP4 is a lookup field (combo-box) which is displaying the
text entries you show, but is bound to a number field in the underlying
lookup table, you will need to test for equality to the number values
corresponding to the text entries (and omit the " delimiters for the
strings). For example, your expression might need to be:
=Iif(([DispositionP4] = 1) OR ([DispositionP4] = 2) OR ([DispositionP4] =
3),Null,IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3])))


"(e-mail address removed).(donotspam)"
Thanks Graham and Rob for your responses. I used Graham's expression. I
have
part of what I need. My problem still is that I get a value for the
unbound
control LastDayAppealStepBP3 even if the bound control Disposition has a
value of "ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA"

I have asked previoulsy how to create a conditional IF compilation that
executes block of code and where would I put that code for an unbound
control. That is, I have created the code below for the Before Update
event
procedure for the unbound control LastDayAppealStepBP3 and the control
source
is
=IIf(IsNull([StepADecisionDateP3]),Null,DateAdd("d",+7,[StepADecisionDateP3])).
The Before Update event procedure for the unbound control
LastDayAppealStepBP3 has no effect when the control Disposition conditions
"ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA" exist. The
control source is controlling what values are displayed. If I delete the
control source there is no value in unbound control LastDayAppealStepBP3.

Private Sub LastDayAppealStepBP3_BeforeUpdate(Cancel As Integer)

'If the bound control DispositionP3 has a value that equals
"ResolvedInformalStepA" then
If (DispositionP4) = "ResolvedInformalStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals "ResolvedStepA"
then
ElseIf (DispositionP4) = "ResolvedStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"WithdrawnStepA"
then
ElseIf (DispositionP4) = "WithdrawnStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Else the unbound control LastDayAppealStepBP3 should perform the
expression
= IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3]))
Else
LastDayAppealStepBP3 = IIf(IsNull([StepADecisionDateP3]), Null,
DateAdd("d",
7, [StepADecisionDateP3]))
End If
End Sub

Thanks
--
Bob


Graham Mandeno said:
Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
If the following conditions can exist for the control
LastDayAppealStepB.
If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control
StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date for
the
control LastDayAppealStepB except when the 3 conditions above exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is
null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))).
I got an
error message that this expression contains the wrong number of
arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])" is
causing the problem. I've tried everything I know to get it to work. So
can
someone break
down this expression into segments and explain what each segment is
trying
to do.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Minor correction to previous statement (that you need two nested Iif
statements):

In this case, you can use a single Iif statement, since you have only two
possible outcomes. This will also work:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] = "WithdrawnStepA") OR
IsNull([StepADecisionDateP3]), Null, DateAdd("d", 7, [StepADecisionDateP3]))

Rob


Rob Parker said:
Hi Bob,

You don't need any code to do this; all you need is the correct expression
in the unbound control LastDayAppealStepBP3. Since the value you want is
dependent on two different bound controls, you will need (at least) two
Iif statements in the expression in your unbound control, nested one
within the other.

Try this:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] =
"WithdrawnStepA"),Null,IIf(IsNull([StepADecisionDateP3]),Null,
DateAdd("d", 7, [StepADecisionDateP3])))

HTH,

Rob

PS. If DispositionP4 is a lookup field (combo-box) which is displaying
the text entries you show, but is bound to a number field in the
underlying lookup table, you will need to test for equality to the number
values corresponding to the text entries (and omit the " delimiters for
the strings). For example, your expression might need to be:
=Iif(([DispositionP4] = 1) OR ([DispositionP4] = 2) OR ([DispositionP4] =
3),Null,IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3])))


"(e-mail address removed).(donotspam)"
Thanks Graham and Rob for your responses. I used Graham's expression. I
have
part of what I need. My problem still is that I get a value for the
unbound
control LastDayAppealStepBP3 even if the bound control Disposition has a
value of "ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA"

I have asked previoulsy how to create a conditional IF compilation that
executes block of code and where would I put that code for an unbound
control. That is, I have created the code below for the Before Update
event
procedure for the unbound control LastDayAppealStepBP3 and the control
source
is
=IIf(IsNull([StepADecisionDateP3]),Null,DateAdd("d",+7,[StepADecisionDateP3])).
The Before Update event procedure for the unbound control
LastDayAppealStepBP3 has no effect when the control Disposition
conditions
"ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA" exist. The
control source is controlling what values are displayed. If I delete the
control source there is no value in unbound control LastDayAppealStepBP3.

Private Sub LastDayAppealStepBP3_BeforeUpdate(Cancel As Integer)

'If the bound control DispositionP3 has a value that equals
"ResolvedInformalStepA" then
If (DispositionP4) = "ResolvedInformalStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"ResolvedStepA"
then
ElseIf (DispositionP4) = "ResolvedStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"WithdrawnStepA"
then
ElseIf (DispositionP4) = "WithdrawnStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Else the unbound control LastDayAppealStepBP3 should perform the
expression
= IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3]))
Else
LastDayAppealStepBP3 = IIf(IsNull([StepADecisionDateP3]), Null,
DateAdd("d",
7, [StepADecisionDateP3]))
End If
End Sub

Thanks
--
Bob


Graham Mandeno said:
Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
If the following conditions can exist for the control
LastDayAppealStepB.
If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control
StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date for
the
control LastDayAppealStepB except when the 3 conditions above exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is
null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))).
I got an
error message that this expression contains the wrong number of
arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])" is
causing the problem. I've tried everything I know to get it to work.
So
can
someone break
down this expression into segments and explain what each segment is
trying
to do.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Hi Rob:

Thanks for the help. At first I could not get your expression to work. Then
I looked at what I told you the control Disposition should equaled compared
to what I should have said the control Disposition actually equaled. It
should have been "Resolved Informal Step A" or "Resolved Step A" or
"Withdrawn Step A". I used this expression and it works perfectly:

=IIf(([DispositionP4]="Resolved Informal Step A") Or
([DispositionP4]="Resolved Step A") Or ([DispositionP4]="Withdrawn Step A")
Or IsNull([StepADecisionDateP3]),Null,DateAdd("d",7,[StepADecisionDateP3]))

Sorry for the error. After looking at the logic of your construct for 4
hours I realized that my expression was looking for "ResolvedInformalStepA"
when it should have looked for "Resolved Informal Step A".

When I was trying to learn COBOL, FORTRAN, and PCL back in the olden days ,
my instructor said that computers are stupid. They can do things very, very
fast but they are still stupid. If you tell a computer to look for something,
it only looks for what you asked it look for. Lesson learned I hope.

Thanks again,
Bob
--
Bob


Rob Parker said:
Minor correction to previous statement (that you need two nested Iif
statements):

In this case, you can use a single Iif statement, since you have only two
possible outcomes. This will also work:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] = "WithdrawnStepA") OR
IsNull([StepADecisionDateP3]), Null, DateAdd("d", 7, [StepADecisionDateP3]))

Rob


Rob Parker said:
Hi Bob,

You don't need any code to do this; all you need is the correct expression
in the unbound control LastDayAppealStepBP3. Since the value you want is
dependent on two different bound controls, you will need (at least) two
Iif statements in the expression in your unbound control, nested one
within the other.

Try this:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] =
"WithdrawnStepA"),Null,IIf(IsNull([StepADecisionDateP3]),Null,
DateAdd("d", 7, [StepADecisionDateP3])))

HTH,

Rob

PS. If DispositionP4 is a lookup field (combo-box) which is displaying
the text entries you show, but is bound to a number field in the
underlying lookup table, you will need to test for equality to the number
values corresponding to the text entries (and omit the " delimiters for
the strings). For example, your expression might need to be:
=Iif(([DispositionP4] = 1) OR ([DispositionP4] = 2) OR ([DispositionP4] =
3),Null,IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3])))


"(e-mail address removed).(donotspam)"
Thanks Graham and Rob for your responses. I used Graham's expression. I
have
part of what I need. My problem still is that I get a value for the
unbound
control LastDayAppealStepBP3 even if the bound control Disposition has a
value of "ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA"

I have asked previoulsy how to create a conditional IF compilation that
executes block of code and where would I put that code for an unbound
control. That is, I have created the code below for the Before Update
event
procedure for the unbound control LastDayAppealStepBP3 and the control
source
is
=IIf(IsNull([StepADecisionDateP3]),Null,DateAdd("d",+7,[StepADecisionDateP3])).
The Before Update event procedure for the unbound control
LastDayAppealStepBP3 has no effect when the control Disposition
conditions
"ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA" exist. The
control source is controlling what values are displayed. If I delete the
control source there is no value in unbound control LastDayAppealStepBP3.

Private Sub LastDayAppealStepBP3_BeforeUpdate(Cancel As Integer)

'If the bound control DispositionP3 has a value that equals
"ResolvedInformalStepA" then
If (DispositionP4) = "ResolvedInformalStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"ResolvedStepA"
then
ElseIf (DispositionP4) = "ResolvedStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"WithdrawnStepA"
then
ElseIf (DispositionP4) = "WithdrawnStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Else the unbound control LastDayAppealStepBP3 should perform the
expression
= IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3]))
Else
LastDayAppealStepBP3 = IIf(IsNull([StepADecisionDateP3]), Null,
DateAdd("d",
7, [StepADecisionDateP3]))
End If
End Sub

Thanks
--
Bob


:

Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
If the following conditions can exist for the control
LastDayAppealStepB.
If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control
StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date for
the
control LastDayAppealStepB except when the 3 conditions above exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is
null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))).
I got an
error message that this expression contains the wrong number of
arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])" is
causing the problem. I've tried everything I know to get it to work.
So
can
someone break
down this expression into segments and explain what each segment is
trying
to do.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Been there, done that ;-)

Glad it's working now

Rob

"(e-mail address removed).(donotspam)"
Hi Rob:

Thanks for the help. At first I could not get your expression to work.
Then
I looked at what I told you the control Disposition should equaled
compared
to what I should have said the control Disposition actually equaled. It
should have been "Resolved Informal Step A" or "Resolved Step A" or
"Withdrawn Step A". I used this expression and it works perfectly:

=IIf(([DispositionP4]="Resolved Informal Step A") Or
([DispositionP4]="Resolved Step A") Or ([DispositionP4]="Withdrawn Step
A")
Or
IsNull([StepADecisionDateP3]),Null,DateAdd("d",7,[StepADecisionDateP3]))

Sorry for the error. After looking at the logic of your construct for 4
hours I realized that my expression was looking for
"ResolvedInformalStepA"
when it should have looked for "Resolved Informal Step A".

When I was trying to learn COBOL, FORTRAN, and PCL back in the olden days
,
my instructor said that computers are stupid. They can do things very,
very
fast but they are still stupid. If you tell a computer to look for
something,
it only looks for what you asked it look for. Lesson learned I hope.

Thanks again,
Bob
--
Bob


Rob Parker said:
Minor correction to previous statement (that you need two nested Iif
statements):

In this case, you can use a single Iif statement, since you have only two
possible outcomes. This will also work:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] = "WithdrawnStepA") OR
IsNull([StepADecisionDateP3]), Null, DateAdd("d", 7,
[StepADecisionDateP3]))

Rob


message
Hi Bob,

You don't need any code to do this; all you need is the correct
expression
in the unbound control LastDayAppealStepBP3. Since the value you want
is
dependent on two different bound controls, you will need (at least) two
Iif statements in the expression in your unbound control, nested one
within the other.

Try this:
=Iif(([DispositionP4] = "ResolvedInformalStepA") OR ([DispositionP4] =
"ResolvedStepA") OR ([DispositionP4] =
"WithdrawnStepA"),Null,IIf(IsNull([StepADecisionDateP3]),Null,
DateAdd("d", 7, [StepADecisionDateP3])))

HTH,

Rob

PS. If DispositionP4 is a lookup field (combo-box) which is displaying
the text entries you show, but is bound to a number field in the
underlying lookup table, you will need to test for equality to the
number
values corresponding to the text entries (and omit the " delimiters for
the strings). For example, your expression might need to be:
=Iif(([DispositionP4] = 1) OR ([DispositionP4] = 2) OR ([DispositionP4]
=
3),Null,IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3])))


"(e-mail address removed).(donotspam)"
Thanks Graham and Rob for your responses. I used Graham's expression.
I
have
part of what I need. My problem still is that I get a value for the
unbound
control LastDayAppealStepBP3 even if the bound control Disposition has
a
value of "ResolvedInformalStepA" or "ResolvedStepA" or
"WithdrawnStepA"

I have asked previoulsy how to create a conditional IF compilation
that
executes block of code and where would I put that code for an unbound
control. That is, I have created the code below for the Before Update
event
procedure for the unbound control LastDayAppealStepBP3 and the control
source
is
=IIf(IsNull([StepADecisionDateP3]),Null,DateAdd("d",+7,[StepADecisionDateP3])).
The Before Update event procedure for the unbound control
LastDayAppealStepBP3 has no effect when the control Disposition
conditions
"ResolvedInformalStepA" or "ResolvedStepA" or "WithdrawnStepA" exist.
The
control source is controlling what values are displayed. If I delete
the
control source there is no value in unbound control
LastDayAppealStepBP3.

Private Sub LastDayAppealStepBP3_BeforeUpdate(Cancel As Integer)

'If the bound control DispositionP3 has a value that equals
"ResolvedInformalStepA" then
If (DispositionP4) = "ResolvedInformalStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"ResolvedStepA"
then
ElseIf (DispositionP4) = "ResolvedStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Or if bound control DispositionP3 has a value that equals
"WithdrawnStepA"
then
ElseIf (DispositionP4) = "WithdrawnStepA" Then
'the unbound control LastDayAppealStepBP3 value is null.
LastDayAppealStepBP3 = IsNull

'Else the unbound control LastDayAppealStepBP3 should perform the
expression
= IIf(IsNull([StepADecisionDateP3]),Null, DateAdd("d", 7,
[StepADecisionDateP3]))
Else
LastDayAppealStepBP3 = IIf(IsNull([StepADecisionDateP3]), Null,
DateAdd("d",
7, [StepADecisionDateP3]))
End If
End Sub

Thanks
--
Bob


:

Hi Bob

The problem is not with DateAdd or IsNull, but with the value 0.

A date/time value of 0 means "midnight on 30 Dec 1899".
By default, this is formatted as 12:00:00 AM.

If you want to specify "no date/time", you should use Null:

=IIf(IsNull([StepADecisionDate]),Null,DateAdd("d",+7,[StepADecisionDate]))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed).(donotspam)"
If the following conditions can exist for the control
LastDayAppealStepB.
If
these conditions exist there should be no date value in the control
LastDayAppealStepB if there is a value in the control
StepADecisionDate:

1. Disposition= ResolvedInformalStepA and CurrentStatus=Resolved
2. Disposition= ResolvedStepA and CurrentStatus=Resolved
3. Disposition= WithdrawnStepA and CurrentStatus=Resolved

I've tried the following expression which I want to give me a date
for
the
control LastDayAppealStepB except when the 3 conditions above
exist:

=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate]))
returns the value 12:00:00 AM when the control StepADecisionDate is
null.
Otherwise
the value for the control LastDayAppealStepB is correct.

I've tried the expression:
=IIf(IsNull([StepADecisionDate]),0,DateAdd("d",+7,[StepADecisionDate],nz([LastDayAppealStepB],[StepADecisionDate]))).
I got an
error message that this expression contains the wrong number of
arguments.

It is obivious that ",nz([LastDayAppealStepB],[StepADecisionDate])"
is
causing the problem. I've tried everything I know to get it to
work.
So
can
someone break
down this expression into segments and explain what each segment is
trying
to do.

Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top