report problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stephendeloach - 09 Jul 2007 16:55 GMT

In my report I have a NetAmount which is =Nz([Quantity],0)*Nz([UnitPrice],0)
Sometimes I dont want to have to enter anything in the Quantity field. Is
there a way I can
just enter the UnitPrice in the form and in the report under NetAmount it
pulls the UnitPrice to the NetAmount?

Reply to this Message

Jeff Boyce - 09 Jul 2007 17:34 GMT

"Sometimes"...

How will Access know if this is one of those times?

Are you saying you want to display the Unit Price even if there is no
quantity?

If so, couldn't you have a UnitPrice control, a Quantity control, and a
NetAmount control (which would be calculated based on the values in the
other two)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

> In my report I have a NetAmount which is
> =Nz([Quantity],0)*Nz([UnitPrice],0)
> Sometimes I dont want to have to enter anything in the Quantity field. Is
> there a way I can
> just enter the UnitPrice in the form and in the report under NetAmount it
> pulls the UnitPrice to the NetAmount?

Reply to this Message

John W. Vinson - 09 Jul 2007 19:12 GMT

>In my report I have a NetAmount which is =Nz([Quantity],0)*Nz([UnitPrice],0)
>Sometimes I dont want to have to enter anything in the Quantity field. Is
>there a way I can
>just enter the UnitPrice in the form and in the report under NetAmount it
>pulls the UnitPrice to the NetAmount?

Sounds like you want to have NULL quantities treated as 1 rather than as 0 -
if so, you could use

NZ([quantity], 1)

in the above expression, defaulting to a quantity of 1 (making the netamount
equal to the unitprice).

John W. Vinson [MVP]

Reply to this Message

stephendeloach - 11 Jul 2007 15:29 GMT

Nz([Quantity],1) gets #ERROR msg. Here is an example of what I need... in my
report i have a Quantity... Description... UnitPrice....
Netamount.
0 FREE pipe $2,901
$0.00
6 Hrs. Del OIL $125
$750.00

How do i make the 0 not show, but still give me the Netamount..(as its not
doing right now...)

Thanks

>>In my report I have a NetAmount which is =Nz([Quantity],0)*Nz([UnitPrice],0)
>>Sometimes I dont want to have to enter anything in the Quantity field. Is
[quoted text clipped - 11 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 11 Jul 2007 19:50 GMT

>Nz([Quantity],1) gets #ERROR msg. Here is an example of what I need... in my
>report i have a Quantity... Description... UnitPrice....
[quoted text clipped - 6 lines]
>How do i make the 0 not show, but still give me the Netamount..(as its not
>doing right now...)

Please post the SQL of the query you're using. I can't see it from here.

John W. Vinson [MVP]

Reply to this Message

stephendeloach - 11 Jul 2007 19:56 GMT

SELECT Charges.UnitPrice, Charges.Quantity, Charges.Description, Charges.
[Invoice#] AS [Charges_Invoice#], Charges.TaxableChk, Table1.[BillTo:],
Table1.WellName, Table1.[Parish/County], Table1.InvoiceDate, Table1.[Invoice#]
AS [Table1_Invoice#], Table1.NetAmount, Table1.CompanyFrom, Table1.Tax,
Charges.Expendable, Table1.RigName, Table1.[TSWSin#], Table1.Date, Table1.
AmtPaid, Table1.Balance, Charges.netamt, Table1.Expendables, Charges.test,
Charges.exp
FROM Table1 INNER JOIN Charges ON Table1.[Invoice#] = Charges.[Invoice#]
WHERE (((Table1.[Invoice#])=[forms]![ParamForms]![Invoice #]));

>>Nz([Quantity],1) gets #ERROR msg. Here is an example of what I need... in my
>>report i have a Quantity... Description... UnitPrice....
[quoted text clipped - 5 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 12 Jul 2007 00:24 GMT

>How do i make the 0 not show, but still give me the Netamount..(as its not
>doing right now...)

What's actually IN the table for this record's Quantity value? A 0 (which you
want to print as a blank) or a Null?

You might try

Your query is:

SELECT Charges.UnitPrice, Charges.Quantity, Charges.Description, Charges.
[Invoice#] AS [Charges_Invoice#], Charges.TaxableChk, Table1.[BillTo:],
Table1.WellName, Table1.[Parish/County], Table1.InvoiceDate, Table1.[Invoice#]
AS [Table1_Invoice#], Table1.NetAmount, Table1.CompanyFrom, Table1.Tax,
Charges.Expendable, Table1.RigName, Table1.[TSWSin#], Table1.Date, Table1.
AmtPaid, Table1.Balance, Charges.netamt, Table1.Expendables, Charges.test,
Charges.exp
FROM Table1 INNER JOIN Charges ON Table1.[Invoice#] = Charges.[Invoice#]
WHERE (((Table1.[Invoice#])=[forms]![ParamForms]![Invoice #]));

Nowhere in this query are you using NZ or any expression. I'm not sure what
you expect!

John W. Vinson [MVP]

Reply to this Message

stephendeloach - 12 Jul 2007 15:15 GMT

Numbers are in the table for Quantity... So where would i put an expression
in the query?

>>How do i make the 0 not show, but still give me the Netamount..(as its not
>>doing right now...)
[quoted text clipped - 20 lines]
>
> John W. Vinson [MVP]

Reply to this Message