Check if a query returns a blank cell and replace with 0

Question

I've written a query in access and it returns a blank cell for most of the results, I need to replace these blank cells with a 0

The first way I tried was:

TtlSickDays: IIf([TotalSickDays]='',0,[TotalSickDays])

But I got the error message: Data type mismatch in criteria expression

Then I tried

TtlSickDays: CInt(IIf(Len([TotalSickDays])=0,0,[TotalSickDays]))

But then I got another error message: Invalid use of null

So I assumed the blank was returning 'Null'

so I tried:

TtlSickDays: CInt(IIf([TotalSickDays]=Null,0,[TotalSickDays]))

But got the same error message...

So I'm a little stumped on what else to try

I edit the bit of sql in an area like this:

enter image description here

Any help on the situation would be greatly appreciated

score 0 · Accepted Answer · answered Nov 12 '13 at 11:30

0

try like that:

TtlSickDays: CInt(IIf(IsNull([TotalSickDays]),0,[TotalSickDays]))

answered Nov 12 '13 at 11:30

Dom84

Thanks, worked perfectly! I had tried LEN and IsBlank but IsNull was what I needed, thanks a lot. – Nov 12 '13 at 11:32
@Sythnet if it is the answer, would you please be so kind and mark it as one? thanks – Dom84 Nov 12 '13 at 11:33
Will do, because my question is still new, I have to wait 7 minutes before I can make it the answer (3 more minutes) – Nov 12 '13 at 11:35
oh sry, i didn't knew that. – Dom84 Nov 12 '13 at 11:42

1 Answers1