Suppressing #Error returned as a query result

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:

Code:

---

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:

Code:

---

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.

Try IFF(NOT IsNull(DueDate),...............)

The function in Access is IIf, not IFF.

Error is triggered by DateAdd function getting Null value in first date. Try: -

Code:

---

Weekday(Nz([DueDate],Date()))+2

---

The IIf() should not be needed if Nz() is used.

I don't allow empty string in fields. I would return Null instead.

another alternative:

WeekBeginDate: Format(IIf(IsNull([DueDate]),Null,DateAdd("d",-Weekday([DueDate])+2,[DueDate])),"mmm dd")

This works:

Code:

---

WeekBeginDate: DateAdd("d",Nz(-Weekday([DueDate]))+2,[DueDate])

---

Thanks for all the input and help.