Iif Is Null?

[rbbakeriii]

I am trying to create an AR Aging report in MS Access and I am having some issues with the If statement -- The [TERM_NET_DAYS] gives me the terms for the invoice (30, 60) but if the Invoice is COD, it returns a blank. So I am trying to make the [DUE_DATE] field = The [Invoice_Date] if blank

DUE_DATE: IIf(IsNull([dbo.RECEIVABLE].[TERMS_NET_DAYS]),[INVOICE_DATE],[INVOICE_DATE]+[dbo_RECEIVABLE].[TERMS_NET_DAYS])

I am getting the following error...

" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long


I have no clue why that error is popping up, I thought it might be due to another field I have, but the Query runs fine if I remove it.

 

[james_lankford]

you have

dbo.RECEIVABLE

and also

dbo_RECEIVABLE

 

[rbbakeriii]

you have

dbo.RECEIVABLE

and also

dbo_RECEIVABLE

Oops, I just fixed but I am still getting that same error

 

[Joe4]

How about just using the Nz function instead to convert the Null to zero, i.e.

[COLOR=#333333]DUE_DATE: [/COLOR][COLOR=#333333][INVOICE_DATE]+Nz([dbo_RECEIVABLE].[TERMS_NET_DAYS],0)[/COLOR]

 

[rbbakeriii]

How about just using the Nz function instead to convert the Null to zero, i.e.

[COLOR=#333333]DUE_DATE: [/COLOR][COLOR=#333333][INVOICE_DATE]+Nz([dbo_RECEIVABLE].[TERMS_NET_DAYS],0)[/COLOR]

YOU ROCK! THANKS!

This forum is the best.

 

[Joe4]

You are welcome!

 

[james_lankford]

I still want to know what the original problem was

 

[Joe4]

I find Nulls to be messy to work with. Between formulas in queries and VBA, I can never remember if I want to use:

IsNull([Field])
[Field] is Null
[Field] = Null

And of course Nulls, Blanks, and Empty are not always treated the same.
So I just try to avoid them whenever I can.

And the Nz function really is a handy little tool...