iif statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pigsy101 - 31 May 2007 17:02 GMT

I'm trying to create a calculated field on a form that tracks stock
levels.

i have three subforms - frmOpeningStock, frmOrdered and frmAllocated.

i'm using the following statement which works fine when all fields are
populated and works when neither the Total Ordered or the Total
Allocated fields are populated.

=IIf(IsNull([frmOrdered].[Form]![Total Ordered]) And
IsNull([tblAllocated].[Form]![Total Allocated]),[frmOpeningStock].
[Form]![number],[frmOpeningStock].[Form]![number]+[frmOrdered].[Form]!
[Total Ordered]-[tblAllocated].[Form]![Total Allocated])

How can i get this to work when either the Total Ordered or the Total
Allocated could be null??

Thanks in advance

P

Reply to this Message

Al Campagna - 31 May 2007 17:13 GMT

pigsy
Try the NZ function... see Help.

> =IIf(IsNull([frmOrdered].[Form]![Total Ordered]) And
> IsNull([tblAllocated].[Form]![Total Allocated]),[frmOpeningStock].
> [Form]![number],[frmOpeningStock].[Form]![number]+NZ([frmOrdered].[Form]!
> [Total Ordered])-NZ([tblAllocated].[Form]![Total Allocated]))

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

> I'm trying to create a calculated field on a form that tracks stock
> levels.
[quoted text clipped - 16 lines]
>
> P

Reply to this Message

Ken Sheridan - 31 May 2007 17:35 GMT

I don't think you need to call the IIf function at all, do you? A simple
expression returning zeros where the controls are Null should give the same
result:

=[frmOpeningStock].[Form]![number]+Nz([frmOrdered].[Form]![Total
Ordered],0)-Nz([tblAllocated].[Form]![Total Allocated],0)

Ken Sheridan
Stafford, England

> I'm trying to create a calculated field on a form that tracks stock
> levels.
[quoted text clipped - 16 lines]
>
> P

Reply to this Message