If Qty = 0...

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.

--
Message posted via http://www.accessmonster.com

stephendeloach via AccessMonster.com wrote:

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);""

--
Marsh
MVP [MS Access]

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

Marshall Barton wrote:
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);""


--
Message posted via http://www.accessmonster.com

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


"stephendeloach via AccessMonster.com" wrote:

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

Marshall Barton wrote:
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);""


--
Message posted via http://www.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...

Klatuu wrote:
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);""

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

stephendeloach via AccessMonster.com wrote:

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?

--
Marsh
MVP [MS Access]

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


"stephendeloach via AccessMonster.com" wrote:

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

Klatuu wrote:
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);""

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

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

Marshall Barton wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

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!

Klatuu wrote:
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]
and the NA text box:
$#,##0.00;($#,##0.00);""

--
Message posted via http://www.accessmonster.com