IFF help for a noobie please!

[Nexus13]

Hey everyone,



I've been playing around with the syntax for this IFF for over 30 minutes and can't seem to get this damn thing working:

From what i've read:
IFF(condition, then, else)
is that correct?

background:
The control source for a text box in one of my forms is currently this:
=DSum("LineCost","MaterialQ","WorkOrderID=" & [WorkOrderID])

What I'm trying to do is implement that IFF statement so that if the the value of that code above is null for a certain record, then put a value of 0 in its place.

The name for the text box is "MaterialCost" and it is pulling data from a query called MaterialQ.

This was the IFF statement I was trying:

=IFF (IsNull ([MaterialCost]), 0, =DSum("LineCost","MaterialQ","WorkOrderID=" & [WorkOrderID]))

Any guidance would be greatly appreciated.

thanks!

[pbaldy]

The crucial problem is that it's IIf, not IFF. You also don't want the = by the DSum. Try

=IIf(IsNull([MaterialCost]), 0, DSum("LineCost","MaterialQ","WorkOrderID=" & [WorkOrderID]))

[Nexus13]

hmmm no dice.

i'm wondering if it has to do with the fact that i'm dsum and IIF can't be combined in the same control source.

I was thinking of adding some vb code instead to help control the final value in the text box.

This was what I did:

Dim SumPrice
SumPrice = DSum("LinePrice", "MaterialQ", "WorkOrderID=" & [WorkOrderID])

If IsNull(SumPrice) Then
SumPrice = 0
End If


How do I get the 'SumPrice' Varible I made to output to the textbox?

[pbaldy]

You should be able to do the original, and I would note that in your last post you used a different field name than originally. If you want to go the second way:

Me.TextboxName = SumPrice

[Nexus13]

So using the original method with the inline IIF statement and Dsum equation all in the control source, I am able to get the DSum segment to work, but when Dsum gives a Null value (i.e. when there is nothing in the MaterialQ for it to grab), the IIF statement is not outputting the 0 value Like I want it to.

hrm...

here is the background on what my form is doing:

On my WORKORDER form, there are 2 components to the total price.

1.) The Labour cost which I manually input.

2.) The Materials cost which is calculated. The 'materials' textbox in my workorder form dsums all the costs for materials used in that particular work order.

Some jobs don't require materials and only have a labour cost so i want the Materials to yield a value of $0.00.

would the fact that the Format for that Textbox is currency matter?

Thanks for your assistance by the way!

[Nexus13]

Sweet SUCCESS!!!

=IIf(IsNull(DSum("LinePrice","MaterialQ","WorkOrde rID=" & [WorkOrderID])),0,DSum("LinePrice","MaterialQ","WorkOrderID=" & [WorkOrderID]))

Appreciate your time again!

[pbaldy]

Glad you got it sorted out. In your earlier effort, you were testing a different field in the IsNull() than you were using in the DSum(), which left you open to that Null problem. You can probably just wrap the DSum() in an Nz() function to avoid the Null in that.

[Nexus13]

Glad you got it sorted out. In your earlier effort, you were testing a different field in the IsNull() than you were using in the DSum(), which left you open to that Null problem. You can probably just wrap the DSum() in an Nz() function to avoid the Null in that.

Do i still need the IIF statement if using Nz()?

[pbaldy]

I don't think so. Try

=Nz(DSum("LinePrice","MaterialQ","WorkOrderID=" & [WorkOrderID]), 0)

[Nexus13]

yah that works great too!

Seems much simpler than the way I did it. Will keep it that way