473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Zero suppression in a report on calculated fields

I have a Purchase Order where I have a calculated field called Price
that is calculated by multiplying Qty * Sales Price. I have 10 of
these fields...Price 1 - Price 10 (for each line of the PO). The price
fields are summed into another calculated field of Total Price (for the
whole purchase order). My problem is that on the purchase order when
there isn't anything in lines 2-9, zeros print on the PO in the Sales
Price and Price fields(since there isn't anything to calculate). Is
there a way to suppress the printing of the zeros?

Apr 3 '06 #1
2 4962
On 3 Apr 2006 12:30:49 -0700, Ryker wrote:
I have a Purchase Order where I have a calculated field called Price
that is calculated by multiplying Qty * Sales Price. I have 10 of
these fields...Price 1 - Price 10 (for each line of the PO). The price
fields are summed into another calculated field of Total Price (for the
whole purchase order). My problem is that on the purchase order when
there isn't anything in lines 2-9, zeros print on the PO in the Sales
Price and Price fields(since there isn't anything to calculate). Is
there a way to suppress the printing of the zeros?


You can use the Control's Format property to suppress 0's.
Set the Format property to:
#,##0.00;-#,##0.00,""

See Access help
Format Property + Number and Currency datatypes
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Apr 3 '06 #2
"Ryker" <ra*****@choctaw.org> wrote in
news:11*********************@i40g2000cwc.googlegro ups.com:
I have a Purchase Order where I have a calculated field called
Price that is calculated by multiplying Qty * Sales Price. I
have 10 of these fields...Price 1 - Price 10 (for each line of
the PO). The price fields are summed into another calculated
field of Total Price (for the whole purchase order). My
problem is that on the purchase order when there isn't
anything in lines 2-9, zeros print on the PO in the Sales
Price and Price fields(since there isn't anything to
calculate). Is there a way to suppress the printing of the
zeros?

Your table structure is not normalized and is causing your
problem.
The PO table should contain the common info, like DateOfOrder,
Buyer, Vendor, etc.
You need a child table for the POLine data, one for each line on
the given PO. It needs the PO_ID,Poline, Article,
SalesPrice,Price,Qty, and other niceties like promisedate,

You set a relation from PO (one) to POLine (many).
Then you only have the number of real lines showing up on your
form and report, so you don't get the calculated zeroes where
they don't belong.
--
Bob Quintal

PA is y I've altered my email address.
Apr 3 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Jacob Barnett | last post by:
Access 2000 main report does not display subreport data in preview and may or may not print... sometimes. Usually, the entire report is fine. The behavior does not seem to depend on particular...
3
by: CSDunn | last post by:
Hello, I have a situation in which each record on a report has 40 fields, any one of which can contain a value of 'C', or 'I', or 'B'. I need to create one calculated field on the report that will...
6
by: DS | last post by:
How do you get a sumon a calculated field in a detail section on a report? I have a group footer that I need the sum on. Thanks DS
7
by: manning_news | last post by:
I've got a report that's not sorting correctly. I build a SQL statement and assign it to the recordsource in the Open event, sorting the data the way the user chooses. The user can choose up to 3...
9
by: StuckAndNoClue | last post by:
I'm pretty new to Access and VB and also know fairly little (more like nothing) about SQL. I've got three queries that shows similar fields, the main ones being: FIRST QUERY Name ID Active...
4
by: Jana | last post by:
Hi! I have an Access 97 report based on tables linked to an SQL back end. The report has 5 subreports in it (unfortunately unavoidable due to the nature of the report) and performance is quite...
7
by: Sunil Korah | last post by:
Hi, I haven't used access reports much. I have a problem in getting the total of a group. I have 3 fields, ProgName (Program name), Pname (Participant's name) and PCategory (Participant...
1
by: Hugh Middity2 | last post by:
Hello, We have a report printing numeric fields. Is it possible to suppress the field if the value is zero? Thanks
4
by: RG52 | last post by:
Hello All I need some help: I have a small DB in access that has data entered via text boxes on a form. It is for confirming orders and such. There are five sets of entry boxes simply listed as...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.