Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: Null values in queries...

    I am relatively new to SQL and Access...

    I am experiencing a problem that involves 3 queries in my 2000 access db.
    The first 2 queries each perform a different multiplication calculation, and both work fine when I run them individually. But on the third query, I am trying to sum the values derived in the 2 prior queries (grouping by pk RAA_Num), and am finding that if the derived value of EITHER ONE of the first 2 queries is NULL, then on the third query, NEITHER will show up...

    This is the code for that third query I have so far...I am trying to make it so that if a value for one query is null, it will be replaced with 0...

    SELECT Expense.RAA_Num, (Sum(IIf(isnull([AA TOTAL CAR LODGE PERDIEM].AATOTALCARLODGEPERDIEM=0, [AA TOTAL MISC].AATOTALMISC=0)))) AS Expr1

    FROM (Expense INNER JOIN [AA TOTAL CAR LODGE PERDIEM] ON Expense.RAA_Num = [AA TOTAL CAR LODGE PERDIEM].RAA_Num) INNER JOIN [AA TOTAL MISC] ON [AA TOTAL CAR LODGE PERDIEM].RAA_Num = [AA TOTAL MISC].RAA_Num

    GROUP BY Expense.RAA_Num;

    Any suggestions would be greatly appreciated! I am so stuck!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    NZ(column, 0)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51
    Nvl(column,0)
    Cheers

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    NVL is Oracle, not Access.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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