Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Location
    NYC
    Posts
    30

    Red face Unanswered: nz function not working on a report?

    For some reason I can't get the nz function in a textbox on my report

    =nz([2005-12],0)


    [2005-12] is formatted as currency?

    Am I applying the function wrong?

    Thank you in advance for your help!

    Wendy

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Why don't you apply the NZ function on the column in the query for your report?

    That would eliminate the need for putting NZ in the textbox ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    Location
    NYC
    Posts
    30
    Hi Grand Poobah,

    I thought of doing that but the report is based on a crosstab query and for the life of me I really can't get a nz function or a isnull function to work there,... If you can figure that one out you get my genius award for Access!


    Wendy

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well actually you should be able to ... Pull up your crosstab in the QBE (query builder) then rightclick and View the SQL. Insert the NZ function call on the desired column ... Go back to design view and save.

    Ex: ... , SomeColumnNameHere, ...

    ..., NZ(SomeColumnNameHere,0) AS BlahBlahBlah, ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2006
    Location
    NYC
    Posts
    30

    nz function - worked,.. kind of

    Hi Grand,

    That is a new one for me and it worked!!,...

    The only thing now is I can't get the format to currency. I tried both in the query properties and the report properties. Can I pick your brain some more,...


    TRANSFORM nz(Sum([000qryToBeUsed].PurchaseAmount),0) AS SumOfPurchaseAmount
    SELECT [000qryToBeUsed].SalesRep, Count([000qryToBeUsed].SalesRep) AS [No of Deals], Sum([000qryToBeUsed].PurchasePrice) AS [Total Pur Price]
    FROM 000qryToBeUsed
    GROUP BY [000qryToBeUsed].SalesRep
    PIVOT Format([DateFunded],"yyyy-mm");

  6. #6
    Join Date
    May 2006
    Location
    NYC
    Posts
    30

    No It did work

    Hi Grand,

    Actually, I threw in a CCUR function and it worked

    So, I understand what you did. Thanks a million!!

    Wendy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •