iif statements

[pigsy101]

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

 

[Al Campagna]

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]))

--
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.

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

 

[Guest]

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