Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blan Fields in Query (Access 2000/XP)

    Hans,
    This a database you have been helping me with. I have attached a copy of it.
    When I run my queries (qryExpeses Query and qry IncomeQuery) that 'Sums' up all the other Summed queries, there are blank fields which forces the Total field to be blank. It will only total if all records are populated in the query. Is there a way to default the null fields to a zero amount?
    Thanks,
    Bart Putnam
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blan Fields in Query (Access 2000/XP)

    The Nz function can be used for this: Nz(A,[img]/forums/images/smilies/cool.gif[/img] returns A, except if A is Null, then it returns B. To get the SumOfGasCharge, even if there is no record for a particular date, you can use

    CCur(Nz([SumOfGasCharge],0))

    The Nz function replaces SumOfGasCharge by 0 if it is null (lacking), and CCur forces the result to be a currency value. The report must be modified to use the new field names.

    In the attached database, I have modified only qryExpensesQuery and Expense Report this way. You should do the same for Income.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blan Fields in Query (Access 2000/XP)

    Thanks Hans

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blan Fields in Query (Access 2000/XP)

    Thanks much Hans.

    Is there a resource / place I can go to find the various functions (like NZ, CCur, etc.)

    Also, I am curious why the query won't give a record total if one of the fields is blank?

    Again thanks,
    Bart

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blan Fields in Query (Access 2000/XP)

    Type Nz or CCur in a code module or in the Immediate window, then press F1. The online help system will provide information about the function.

    What record total do you mean? The Total field in qryExpensesQuery returns a total even if one or more of the contributing fields is blank (null).

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blan Fields in Query (Access 2000/XP)

    Sorry I haven't gotten back to you sooner.
    I realize I was wrong about totals even with a null field.

    Regards,
    Bart

Posting Permissions

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