If H/A is Null then Net=Gross

[KatieK]

Hi,

Trying to get this query figured out right. I have a Gross, H/A, and Net fields.

If there is no value in H/A then Net = Gross. This is what I've figured out so far. In the Net criteria field in my query...

IIf([H/A]=0,[Net]=[Gross],[Gross]*[H/A])

 

[pbaldy]

Try

IIf([H/A]=0,[Gross],[Gross]*[H/A])

 

[dcx693]

You cannot perform assignments of the type [Net]=[Gross] in an IIf function. The final value of the IIf function gets assigned to something, it cannot perform actions.

Also, the function:

IIf([H/A]=0,[Net]=[Gross],[Gross]*[H/A])

should not be placed in a criteria. It's not a valid criteria for a query.

What exactly are you trying to do with this query? It looks like you're trying to update some of the values.

 

[KatieK]

You cannot perform assignments of the type [Net]=[Gross] in an IIf function. The final value of the IIf function gets assigned to something, it cannot perform actions.

Also, the function:

IIf([H/A]=0,[Net]=[Gross],[Gross]*[H/A])

should not be placed in a criteria. It's not a valid criteria for a query.

What exactly are you trying to do with this query? It looks like you're trying to update some of the values.

I'll try and explain further. In a table, the Gross is entered. If a H/A is determined to exist, that info will populate a field also. For the Net field, I want that to reflect the Gross if there is no value for H/A or if there is a value for H/A, to multiply the Gross * H/A and that equals Net. Does that explain better? Thanks, Katie

 

[dcx693]

Pbaldy has your solution above.

 

[pbaldy]

It occurs to me that HA could be Null, in which case this might work better:

IIf([H/A]=0 OR [H/A] Is Null,[Gross],[Gross]*[H/A])

 

[KatieK]

It occurs to me that HA could be Null, in which case this might work better:

IIf([H/A]=0 OR [H/A] Is Null,[Gross],[Gross]*[H/A])

Works perfect. Thanks so much!

 

[Pat Hartman]

Simplified:

Gross * Nz([H/A],0)

You don't need the IIf() at all.

 

[pbaldy]

Gross * Nz([H/A],0)

Good idea, though anything multiplied by zero would be zero, wouldn't it? Perhaps

Gross * Nz([H/A],1)

though that wouldn't allow for a zero value in H/A

 

[Pat Hartman]

You are correct, I read the IIf() wrong and thought the poster wanted 0.