If Qty = 0...

[stephendeloach via AccessMonster.com]

In my report I have a Quantity, UnitPrice, and NetAmount. Is there a way I
can have it formatted to something like this.. If Quantity = 0, the NetAmount
= UnitPrice ?

Also, I have a Descriptions field, is there a way I can have the Quantity,
Unitprice, and Netamount BLANK if there is nothing entered in them? So it
dosent look like this..

Qty. Desc. UP NA
0 Example 0 $0.00

i want it to look like this...

Qty. Desc. UP NA
Example


thanks.

 

[Marshall Barton]

In my report I have a Quantity, UnitPrice, and NetAmount. Is there a way I
can have it formatted to something like this.. If Quantity = 0, the NetAmount
= UnitPrice ?

Also, I have a Descriptions field, is there a way I can have the Quantity,
Unitprice, and Netamount BLANK if there is nothing entered in them? So it
dosent look like this..

Qty. Desc. UP NA
0 Example 0 $0.00

i want it to look like this...

Qty. Desc. UP NA
Example

You can make a text box display blank instead of 0 by using
a custom format. For example, the Qty an Up text boxes
could use the custom format:
0;-0;""
and the NA text box:
$#,##0.00;($#,##0.00);""

 

[stephendeloach via AccessMonster.com]

Works perfect! Any suggestion on the first question? Thanks!

In my report I have a Quantity, UnitPrice, and NetAmount. Is there a way I
can have it formatted to something like this.. If Quantity = 0, the NetAmount

[quoted text clipped - 11 lines]

Qty. Desc. UP NA
Example

You can make a text box display blank instead of 0 by using
a custom format. For example, the Qty an Up text boxes
could use the custom format:
0;-0;""
and the NA text box:
$#,##0.00;($#,##0.00);""

 

[Guest]

Make this the control source for the NA control:
=IIf([Quantity] = 0,[UnitPrice],[NetAmount])
--
Dave Hargis, Microsoft Access MVP

Works perfect! Any suggestion on the first question? Thanks!

In my report I have a Quantity, UnitPrice, and NetAmount. Is there a way I
can have it formatted to something like this.. If Quantity = 0, the NetAmount

[quoted text clipped - 11 lines]

Qty. Desc. UP NA
Example

You can make a text box display blank instead of 0 by using
a custom format. For example, the Qty an Up text boxes
could use the custom format:
0;-0;""
and the NA text box:
$#,##0.00;($#,##0.00);""

 

[stephendeloach via AccessMonster.com]

that works but my control source for NA is =Nz([Quantity]*[unitprice],0)
already. So when I enter =IIf([Quantity] = 0,[UnitPrice],[NetAmount]) it
dosent come up with the right NA when there is say a 6 in the unitprice...

Make this the control source for the NA control:
=IIf([Quantity] = 0,[UnitPrice],[NetAmount])

Works perfect! Any suggestion on the first question? Thanks!

[quoted text clipped - 10 lines]

 

[Marshall Barton]

Works perfect! Any suggestion on the first question?

I missed that as a separate question. Now that I reread it,
I don't understand what you want ;-\

Did Klatuu figure it out for you?

 

[Guest]

Aha! ask half a question, get half an answer

=IIf(Nz([Quantity],0) = 0,[UnitPrice],Nz([Quantity],0)*Nz([unitprice],0))

Note about the Nz function:
The way you had it coded would not work correctly. The value has to be
converted before you use it in a calculation. The way is was coded, would,
if Quantity were Null Actually end up as
Null * Unit Price - Which would return Null
Then the Nz function would return 0.
So anytime you hit a Null value in quantity, the result of the calculation
would be 0.
--
Dave Hargis, Microsoft Access MVP

that works but my control source for NA is =Nz([Quantity]*[unitprice],0)
already. So when I enter =IIf([Quantity] = 0,[UnitPrice],[NetAmount]) it
dosent come up with the right NA when there is say a 6 in the unitprice...

Make this the control source for the NA control:
=IIf([Quantity] = 0,[UnitPrice],[NetAmount])

Works perfect! Any suggestion on the first question? Thanks!

[quoted text clipped - 10 lines]

and the NA text box:
$#,##0.00;($#,##0.00);""

 

[stephendeloach via AccessMonster.com]

Yes it worked when there is a 0 as the Quantity, but when there is any other
number it dosent...

 

[stephendeloach via AccessMonster.com]

Worked! Now.. I have another question... On my report in my report footer I
have a Non-Taxable, Taxable, Subtotal, Tax, and Total.. My net amount isnt
being added in now.. Here are my formulas for each..

Non-Taxable =Sum(IIf([taxablechk]=False,[Quantity]*[unitprice],0))

Taxable =Sum(IIf([taxablechk]=True,[Quantity]*[unitprice],0))

Subtotal =Sum(IIf([taxablechk]=False,[Quantity]*[unitprice],0))+Sum(IIf(
[taxablechk]=True,[Quantity]*[unitprice],0))

Tax =Sum(IIf([taxablechk]=True,[Quantity]*[unitprice],0))*[Tax]

Total =Sum(IIf([taxablechk]=False,[Quantity]*[unitprice],0))+Sum(IIf(
[taxablechk]=True,[Quantity]*[unitprice],0))+Sum(IIf([taxablechk]=True,
[Quantity]*[unitprice],0))*[Tax]

[taxablechk] is a check box in the form that when checked it adds the [tax]

i hope i included everything that is needed. thanks for the help!

Aha! ask half a question, get half an answer

=IIf(Nz([Quantity],0) = 0,[UnitPrice],Nz([Quantity],0)*Nz([unitprice],0))

Note about the Nz function:
The way you had it coded would not work correctly. The value has to be
converted before you use it in a calculation. The way is was coded, would,
if Quantity were Null Actually end up as
Null * Unit Price - Which would return Null
Then the Nz function would return 0.
So anytime you hit a Null value in quantity, the result of the calculation
would be 0.

that works but my control source for NA is =Nz([Quantity]*[unitprice],0)
already. So when I enter =IIf([Quantity] = 0,[UnitPrice],[NetAmount]) it

[quoted text clipped - 7 lines]