Re: How to replace "Blank" With "0"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'm guessing that you have a left-join or crosstab query.

I don't know why, but I had problems getting NZ to work on the latter until
I went into Sql view, looked for the field name
I surrounded it by Nz and Val (NZ doesn't always seem to turn it into a
number)

In the example below, I had nulls in the currency field Expend.in a query
called QryPivotAllMonths

In Sql view I found [QryPivotAllMonths].[Expend] and surrounded it so that
it now looked like

Val(NZ([QryPivotAllMonths].[Expend],0)) AS ExpendNZ

(I gave the 'edited' field the new name, ExpendNZ)

The tricky fields were the date, not the number fields. For those, I had to
choose a default date and surround my date field with NZ

DateValue(NZ([XDate],#4/24/2008#)) AS MyDate

so that if there was Null in the XDate field, it read 24/04/08 instead. As
with the numbers, I had to use something to make the date 'real' by using
DateValue

Evi

"Syphonics via AccessMonster.com" <u25414@uwe> wrote in message
news:83ff7e43d2cf4@xxxxxx
I am creating a report base on the filtered results from the query.
Sometimes
there is no results after filtering. The query is total blank. As my
report
is unable to total once there is a blank. How do I replace the query blank
result with "0"?
I have tried Nz([Amount],0) but still it is a blank instead of 0.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200805/1



.

Relevant Pages

  • Re: I get this sign <> in my query
    ... GUESSING here. ... So assuming that you are using a crosstab query and that sign is appearing as a column header then the means that the values that are being used for this column is NULL. ... You can get rid of it by using a where clause that screens out null values in the column. ... Access MVP 2002-2005, 2007-2009 ...
    (microsoft.public.access.queries)
  • Re: Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
    ... Access apparently is too stupid to ... I AM NOTT trying to update the crosstab query itself - only use ... I'm guessing that maybe I need to use a sub-query to work around this, ...
    (comp.databases.ms-access)