Re: Report shows null values, how to convert to a zero

Open the report in design view, and set the Format property of this text box
to something like this:
0;-0;0;0
The 4th value causes it to show a zero for null.

If you actually want a zero value for calculations (not just display), open
the query in SQL View (View menu, in query design), and add Nz() around the
expression in the TRANSFORM clause. For example, if you see:
TRANSFORM Sum(Table1.Quantity) AS SumOfQuantity
change it to:
TRANSFORM CLng(Nz(Sum(Table1.Quantity),0)) AS SumOfQuantity

The CLng() typecasts so JET interprets the value correctly.
Use CDbl() if you need fractional values, or CCur() for currency.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DBenedict" <DBenedict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3A5F6323-A042-4DE8-8311-9EB6D049A6F1@xxxxxxxxxxxxxxxx
> My report comes from a Crosstab query. I have one row Part No. and two
> columns, PASS / FAIL. Many rows have data in both columns, but some only
> have data in one column.
> Example.
> Row 1, Part No has a FAIL of 5 and a PASS of 5.
> Row 2, Part No has a FAIL of 2 and a PASS of "blank or Null".
> I want the Report to show "Row 2 has FAIL of 2 and a PASS of 0".
> I want to convert the "blank", Null or no data fields into a the number
> "0"
> without affecting the column above that actually has a number.
>


.

Relevant Pages

  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: transpose results
    ... You need to transpose or ... transform the data into multiple columns. ... This is just a one-time scenario, so poor db design is not an issue - I'll ... If IdTable is the primary key of the table, the following query will ...
    (microsoft.public.access.queries)
  • Re: Interesting form design problem: help!
    ... open in design view, click View> Sorting and Grouping (or click the toolbar ... records in a report". ... I thought it might be easier if I use a query instead then I can filter ... When I go through the wizard I choose these fields. ...
    (microsoft.public.access.forms)
  • Re: "0.0" Value (not Null)... missing value in report
    ... If you open your table in design view, what data type are Sale_Price, SPC, and B_D_Option_Grp? ... Assuming they are all either Number or Currency, then I don't see any way Access could be misunderstanding the data type in the query. ... I will assume so, which leads us to conclude that the query is now fine, so the problem is with the report. ... 'CalculationChecker' report but produces a blank on my application report ...
    (microsoft.public.access.reports)
  • Re: first attempt at a report - HELP!
    ... Create a new query containing both tblDate and your other table. ... In the upper pane of the query design window, ... Drag TheDate from tblDate into the query. ... Now to use the query in the report. ...
    (microsoft.public.access.reports)