IIf Statement problem

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?
 
J

John Spencer

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
'====================================================
 
S

Stefan_889_12

Hi Sandra,

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

Stefan.
 
D

Dale Fye

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.
 
S

Sandra

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...
 
S

Sandra

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...
 
J

John Spencer

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...
 
S

Sandra

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...
 
J

John Spencer

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...
 
S

Sandra

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Statement no working 2
Iif function with two fields to look at 6
Blanks incorrect 2
Adding a query result to a form in Access 2007 1
dateadd conditional formating to equal yes or no 1
Struggling to perform IF function in property sheet for table in a 7
Date Calculation Problem 2
IIF Statement 2

Top