IIf Statement problem

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
 
IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hi Sandra,

try this:
Overdue2:
IIf(IsNull([ActualDateOfPDA]);"OVERDUE";(IIf([TargetDateOfPDA]<=Now();"OVERDUE";"OK")))

Stefan.
 
Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Stefan, Sorry but it did not work I got the error message:-
you omitted an operand or operator, you entetred an invalid character or
comma or you entered text without surrounding it in question marks.
I tried to do Print screen and paste it into this reply, but this box
wouldn't let me.
Thanks for trying, any other ideas would be gratefull as I am very new to
writting these sort of formulas.



Stefan_889_12 said:
Hi Sandra,

try this:
Overdue2:
IIf(IsNull([ActualDateOfPDA]);"OVERDUE";(IIf([TargetDateOfPDA]<=Now();"OVERDUE";"OK")))

Stefan.

Sandra said:
I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
 
Dale

Thanks for trying but it still doesn't work. It still gives the result of
"Overdue" for target dates which are after todays current date. These fields
should be OK.
if the [TargetDateOfPDA] has not been entered the result of "Overdue" is
correct.


Dale Fye said:
Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sandra said:
I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
 
Did this work?

IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK")

IF not, what was the problem? This should return OverDue if
TargetDateOfPdA on or before today and
ActualDateOfPDA is null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dale

Thanks for trying but it still doesn't work. It still gives the result of
"Overdue" for target dates which are after todays current date. These fields
should be OK.
if the [TargetDateOfPDA] has not been entered the result of "Overdue" is
correct.


Dale Fye said:
Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sandra said:
I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
John
This is brilliant. Thank you so much. The only thing that I would like to
change if possible is if the "TargetDateOfPDA" is blank to give the answer as
"Check for Date"

John Spencer said:
Did this work?

IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK")

IF not, what was the problem? This should return OverDue if
TargetDateOfPdA on or before today and
ActualDateOfPDA is null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dale

Thanks for trying but it still doesn't work. It still gives the result of
"Overdue" for target dates which are after todays current date. These fields
should be OK.
if the [TargetDateOfPDA] has not been entered the result of "Overdue" is
correct.


Dale Fye said:
Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Then you will need to nest the IIF statements. Something like the
following (all on one line):

IIF(TargetDateOfPDA is Null,"Check for Date",
IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John
This is brilliant. Thank you so much. The only thing that I would like to
change if possible is if the "TargetDateOfPDA" is blank to give the answer as
"Check for Date"

John Spencer said:
Did this work?

IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK")

IF not, what was the problem? This should return OverDue if
TargetDateOfPdA on or before today and
ActualDateOfPDA is null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dale

Thanks for trying but it still doesn't work. It still gives the result of
"Overdue" for target dates which are after todays current date. These fields
should be OK.
if the [TargetDateOfPDA] has not been entered the result of "Overdue" is
correct.


:

Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
John

Thank you very much for all of this help, it is now working perfectly.

John Spencer said:
Then you will need to nest the IIF statements. Something like the
following (all on one line):

IIF(TargetDateOfPDA is Null,"Check for Date",
IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John
This is brilliant. Thank you so much. The only thing that I would like to
change if possible is if the "TargetDateOfPDA" is blank to give the answer as
"Check for Date"

John Spencer said:
Did this work?

IIF(TargetDateOfPDA<=Date() AND ActualDateOfPDA is Null,"OverDue","OK")

IF not, what was the problem? This should return OverDue if
TargetDateOfPdA on or before today and
ActualDateOfPDA is null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Sandra wrote:
Dale

Thanks for trying but it still doesn't work. It still gives the result of
"Overdue" for target dates which are after todays current date. These fields
should be OK.
if the [TargetDateOfPDA] has not been entered the result of "Overdue" is
correct.


:

Try:

Overdue:IIF(NOT ISNULL([ActualDateOfPDA]), "OK", "OverDue")

Although you don't indicate what you want to do if the [TargetDateOfPDA] has
not been entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I am trying to create an IIf Statement to show if a date is OK or Overdue.
My fields are "TargetDateOfPDA" and "ActualDateOfPDA". I want to create a new
field (Overdue)within a query which shows OK or OVERDUE. If a target date
has been entered and the Actual date has also been entered the Overdue field
should always show OK, but if the Target Date has been entered and the date
has passed the current date and no date has been filled in on the Actual Date
field then I want it to show "OVERDUE"
The statement which I have been using is as follows:
Overdue:IIf(IsNull([ActualDateOfPDA],
([TargetDateOfPDA]<=Now(),"OK","OVERDUE")
This does not work. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top