Access Week number Null values

[chrisl1471]

Morning all, well for me anyway!

So I have a database that I am running a query that gives me the number of records closed by weeks...so I get the # of the week and the count of the closed records....

I have 1 week where no records were closed, so it is a null value, but in my datasheet it does not show up at all. See my attachment, week # 15 does not show up at all as no records were closed that week.

ReqWeek: DatePart("ww",[Status Date])

I need it to show that, So I was thinking something like this,

(IIf([StatusDate] Is Null,0,[StatusDate]))

But I cannot figure out the correct arguments to combine these? Is that possible?

 

[Minty]

That won't give you the missing week number unfortunately, and unless you are omnipotent you can't guess what the missing week would be.
This is one of those cases where a number table or calendar table can help.

If you have a table with all week numbers it, simply left join it to you current query, and restrict it to >= DatePart("ww",Date()) , this is also normally very efficient.

Your number table can literally be a single field with 52 records in it.