Statement no working

[Lisa]

I am trying to write a statement in a query that will let me know not only if
something is completed and forecasted but also if it is overdue. i.e.
Date Status
1/1/01 Completed
12/1/07 forecasted
7/1/07 overdue

I tried ----
IIf(IsNull([Date]),IIf([Date]<Now(),"Forcasted","Overdue"),"Completed")
But it will not work. All forecasted dates are come back as overdue.

Any ideas??
Any Help would be greatly appreciated.

 

[George Nicholson]

1) its a good idea to avoid using Reserved words for Field names. The list
of Reserved words is lengthy, but you can assume it includes the name of any
VB function like Date. Is this causing a problem in your case? Maybe not,
but why take the chance?

2) As written, your statement logic could be rewritten as:
If IsNull([MyDate]) Then
' [MyDate] is Null
If [MyDate] < Now() Then
' This will never be True
'(Null < anything) = Null
strStatus = "Forecasted"
Else
strStatus = "Overdue"
EndIf
Else
' [MyDate] is NOT Null
strStatus = "Completed"
End If

I don't have enough information to tell you how to change your formula to
get the results you want. Your sample data indicates that if MyDate is >
Now, your desired result is "Forecasted". Fine. But if MyDate < Now() you
want it to be either Overdue or Completed and you have provided no clue as
to how to make that determination. I suspect there is a 2nd Date field
involved.

...., IIf(nz([MyDate],0)<Now(),"Forcasted","Overdue"),...
Would reverse the results: all Null values changed to 0, which will cause
them to be labeled "Forecasted". This means nothing would ever be labeled
"Overdue", which I doubt is your intent.

HTH,

 

[Lisa]

Sorry for the cryptic question… Thank you, your suggestion works great.

1) its a good idea to avoid using Reserved words for Field names. The list
of Reserved words is lengthy, but you can assume it includes the name of any
VB function like Date. Is this causing a problem in your case? Maybe not,
but why take the chance?

2) As written, your statement logic could be rewritten as:
If IsNull([MyDate]) Then
' [MyDate] is Null
If [MyDate] < Now() Then
' This will never be True
'(Null < anything) = Null
strStatus = "Forecasted"
Else
strStatus = "Overdue"
EndIf
Else
' [MyDate] is NOT Null
strStatus = "Completed"
End If

I don't have enough information to tell you how to change your formula to
get the results you want. Your sample data indicates that if MyDate is >
Now, your desired result is "Forecasted". Fine. But if MyDate < Now() you
want it to be either Overdue or Completed and you have provided no clue as
to how to make that determination. I suspect there is a 2nd Date field
involved.

...., IIf(nz([MyDate],0)<Now(),"Forcasted","Overdue"),...
Would reverse the results: all Null values changed to 0, which will cause
them to be labeled "Forecasted". This means nothing would ever be labeled
"Overdue", which I doubt is your intent.

HTH,

I am trying to write a statement in a query that will let me know not only
if
something is completed and forecasted but also if it is overdue. i.e.
Date Status
1/1/01 Completed
12/1/07 forecasted
7/1/07 overdue

I tried ----
IIf(IsNull([Date]),IIf([Date]<Now(),"Forcasted","Overdue"),"Completed")
But it will not work. All forecasted dates are come back as overdue.

Any ideas??
Any Help would be greatly appreciated.