Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    Liverpool - UK
    Posts
    12

    Unanswered: NULLS & NILS - How to use Nz??

    I am a beginner with Access with little knowledge of code and writing SQL!

    I have a query as follows;
    SELECT Sum([Tablename].[Amountfield])As[SumOfAmountfield],
    Count([Tablename].[Locationfield]AS CountOfLocationfield
    FROM[Tablename]
    WHERE.......criteria.

    On some occasions this produces a zero in the CountOfLocationfield but a NULL in the [SumOfAmountfield]

    I need the [SumOfAmountfield] to be zero rather than null (as it is used in a further calculation to provide a grand total).

    The HELP indicates that I use the Nz function to do this but I cannot get the syntax right. Does anybody know how to amenrd the query to produce a zero rather than a null.

    Very confusing these nulls and nils!!!!!

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Just use an IIf statement and the IsNull function to set SumOfAmountfield to 0 is it's Null:

    SELECT IIf(IsNull(Sum([Tablename].[Amountfield])),0,Sum([Tablename].[Amountfield])) AS [SumOfAmountfield],
    Count([Tablename].[Locationfield]) AS CountOfLocationfield
    FROM [Tablename]
    WHERE.......criteria.

  3. #3
    Join Date
    Sep 2004
    Location
    Liverpool - UK
    Posts
    12
    Thank you - appreciate the help

Posting Permissions

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