How to show null values in Pivot Query - Microsoft Community

i have pivot query correctly counts between time range except doesn't show 0.

look for there way show 0's when not records within range.

here current sql code

transform count(usagetimeqry.fname) countoffname
select usagetimeqry.datein
usagetimeqry
(((usagetimeqry.datein)=date()))
group usagetimeqry.datein
order usagetimeqry.datein desc 
pivot iif([timein] between 0.333333333 , 0.583333333,"8-2pm",iif([timein] between 0.583333333 , 0.833333333,"2-8pm"));

i tried using nz in front of ([timein]) no luck.

also saw there possibility change format in query design properties did not see necessary.

thanks!

if there no values within range, need add in clause:

transform nz(count(usagetimeqry.fname),0) countoffname
select usagetimeqry.datein
usagetimeqry
(((usagetimeqry.datein)=date()))
group usagetimeqry.datein
order usagetimeqry.datein desc
pivot iif([timein] between 0.333333333 , 0.583333333,"8-2pm",iif([timein] between 0.583333333 , 0.833333333,"2-8pm")) in("2-8pm","8-2pm");

Office / Access / Windows 10 / Office 365 for business