Search code examples
sqlms-accessexpressionbuilder

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

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

Solution

  • try like that:

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

    MS Access: IsNull Function