I have a query that evaluates a record column "Due date" and returns:
1. The week of of the year index and ..
2. The date of Monday of that week:
So if I entered July 4, 2024, I get the returned values 27 and Jul 1
However, in some record instances, the "Due date" column is not filled in. When I attempt to query under this condition
Name Date Due
Mary July 4, 2024
Bob
I get
27 Jul 1
#Error
These are the two field statements:
WeekIndex: DatePart("ww",[DueDate])
WeekBeginDate: Format(DateAdd("d",-Weekday([DueDate])+2,[DueDate]),"mmm dd")
So it appears if the DueDate in the record is Null then the WeekIndex statement ignore the Null value and returns a Null value but in the case of the WeekBeginDate an error occurs.
I have worked around this problem using:
WeekBeginDate: IIf(IsDate([DueDate]),Format(DateAdd("d",-Weekday([DueDate])+2,[DueDate]),"mmm dd"),"")
Is there a better\more efficient way to suppress that #Error returned if the DueDate field is Null. I know there is a Nz function but unsure how to employ it.
Thank you.