Results 1 to 7 of 7
  1. #1
    WKU is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Oct 2013
    Posts
    12

    How to calculate date cells that are Empty


    I am open to suggestions if you believe that there is a better way of doing this. I have a query that has 11 dates on it, I am trying with iif statements to get it down to one column reflecting the most recent date out of the 11, for example 5/2/14 and 4/9/14, I would want to have 5/2/14. The problem with this is that most of the cells are empty and and I can't calculate empty date cells. my thought was to create a statement that goes something like this iif([Date1]="", "1", [date1]), obviously this does not work but I would like for it to show something other then nothing so that I can calculate on it.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    How about testing for [date]<>""

    Also, should not use reserved words like date as names for fields, objects, etc.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If the field is empty it is probably a Null. Maybe handle Null.

    Nz([Date1],#12/31/2999#)

    Sounds like non-normalized data structure.

    Maybe too many fields for nested IIf. Alternative might be Switch, otherwise build a custom function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    WKU is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Oct 2013
    Posts
    12
    It isn't a normal data structure but the upside is that the amount of information is fairly limited with only a few hundred list items. You're formula created a column with all rows showing the date of 12/31/2999, how would I use it in order to show the actual dates that are there but when it is Null to replace it with 12/31/2999?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    That's what the Nz function should have accomplished. If Date1 is null then return 12/31/2999.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    WKU is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Oct 2013
    Posts
    12
    It only returns 12/31/2999 what could be wrong?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Offhand, that means all records have no data in that field, but if you know there is data, then I cannot explain without analyzing data. If you want to provide, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-15-2014, 02:45 PM
  2. Replies: 1
    Last Post: 11-26-2013, 09:25 AM
  3. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  4. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  5. code to delete rows based of empty cells
    By jcbrackett in forum Programming
    Replies: 3
    Last Post: 03-05-2012, 02:27 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
  •  
Other Forums: Microsoft Office Forums