Suppressing #Error returned as a query result

[gmaxey]

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.

[xps350]

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

[June7]

The function in Access is IIf, not IFF.

Error is triggered by DateAdd function getting Null value in first date. Try: -
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.

[arnelgp]

another alternative:

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

[gmaxey]

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

Thanks for all the input and help.