Forum Discussion
Getting #ERROR in Custom Text Field Using IIf Function For The False Argument
I created a simple IIF function in a custom text field to check whether a task is part of the project baseline using the Baseline Start field. If the task is not part of baseline then the field displays "No", otherwise the field displays a value of "Yes". For some reason, the field for tasks where the value should be displaying "No", are instead displaying #ERROR. Why? Does it have something to do with the NA value in the Baseline start field? If the true part worked, I don't understand why the false part is resulting in #ERROR. Here is the formula I'm using:
IIf([Baseline Start]="NA","No","Yes")
This is what project ends up displaying:
John,
In the digital world dates are represented by numbers. For example, 8/29/22 12:00 PM is stored as 44802.54. String based fields (e.g. Text1) can of course have any string variable (e.g. "NA").
What I don't fully understand is that the latest date possible with Project (current versions) is 12/31/2149 which is represented by: 91312.34, yet using the ">50000" trick still works for "NA".Interestingly enough, an "if" statement in VBA can directly detect "NA" in a date field. For example, the following code works fine:
Sub testNA()
For Each t In ActiveProject.Tasks
t.Text1 = "yes"
If (t.BaselineStart = "NA") Then t.Text1 = "no"
Next t
End Sub
Oh the mysteries of Project 🙂
John
11 Replies
- John,
The error occurs because "NA" is not a string, it is in fact a numerical representation of a date. There are a couple of ways to deal with it, this is the method I use:
Text1=IIf([Baseline Start]>50000,"no","yes")
John- Wow. Thats interesting who would have thought that. Thanks for the tip i'll give it a try.
- John,
Another way to handle it is:
Text1=IIf([Baseline Start]=projDateValue("NA"),"no","yes")
John
