Results 1 to 4 of 4

Dsum() only records with a field that is null

  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Dsum() only records with a field that is null

    I'm not sure this qualifies as a query but it is related.



    Using Dsum() I want to sum up one field only on rows that have a date field that is null. I can't quite figure out how to set that criteria properly.

    A VBAish explanation of what I'm trying to do
    Code:
    If isnull(datefield) then Sum = Sum + Amountfield
    Anyone happen to know?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    if it's in a query:

    Test: iif(isnull(datefield), [number field], 0)

    if your query is an aggregate query you would just then need to set the GROUP BY to sum for this field

  3. #3
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Sorted it out. Somehow I missed trying "Datefield Is Null". or maybe I only tried it with IsNull() or something.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    mark it solved!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. show all records when criteria for DATE field is null.
    By Homegrownandy in forum Queries
    Replies: 7
    Last Post: 05-31-2017, 09:29 AM
  2. Replies: 7
    Last Post: 07-17-2014, 12:42 PM
  3. DSUM Encounters Null Values
    By CementCarver in forum Programming
    Replies: 4
    Last Post: 04-16-2013, 10:25 AM
  4. Replies: 8
    Last Post: 08-13-2012, 04:30 AM
  5. Replies: 6
    Last Post: 03-22-2011, 08:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums