We help IT Professionals succeed at work.

Access 2007, Using a Null value to change another field to 0

Using the following, I'm trying to force a 0 value into the [BillDt-BillPd] field if the [Bill Paid Date] field is empty. I've tried Null as is demonstrated, 0, IsNull before [Bill Paid Date] & NZ before [Bill Paid Date], none of which worked.

Over30: Iif([Bill Paid Date]=Null,[BillDT-BillPd]=0,IIf([BillDt-BillPd]<30,0,IIf([BillDt-BillPd]>30,[BillDt-BillPd]-30)))

Watch Question

Try this
SELECT IIf(IsNull([BillDT-BillPd]),0,iif(len([BillDT-BillPd])<30,0,len([BillDT-BillPd])-30)) AS YourData
FROM employee

Open in new window


I think that worked! Thanks!