Results 1 to 6 of 6
  1. #1
    georgeself is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3

    How to get Field A ( date Field) to show if Filed B (date field is Blank)


    I am hoping someone can help me!
    I have several date fields which i need the query to return based on whether the field has a date.
    If Field A has a date but not B it returns A however,
    If Field B has a date it returns B (even if A has a date)

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In a query you could use

    DateToDisplay : Nz([DateB],[DateA])
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    georgeself is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    hi Minty,

    It is returning that the expression entered has an invalid.(dot) or ! operator or invalid Parentheses?

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you paste exactly what you used in the query ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    georgeself is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    DateToDisplay : Nz([QI Alerts Query_3rd Due:3rd Due],[QI Alerts Query_2nd Due:2nd Due])

    I have Fields as below
    1st Quarter 2nd Due 2nd Quarter 3rd Due

    I only enter actual dates into the Quarter fields and the due dates automatically populate. I want to pull out the due dates, but only show the most recent due date. i.e if the 2nd quarter has not yet been completed there will be no date in this or the 3rd due field so I want it to return the 2nd Due, but if this has been completed the 3rd Due will be the date I want returned.

    Is this possible?????

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    You need to use an IIf() or Switch() function instead of Nz().

    IIf([2nd Quarter] Is Null, [2nd Due], [3rd Due])
    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: 4
    Last Post: 08-25-2016, 04:19 PM
  2. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  3. Replies: 20
    Last Post: 01-13-2015, 02:23 PM
  4. How to query if date field is left blank?
    By McArthurGDM in forum Queries
    Replies: 9
    Last Post: 08-04-2014, 05:18 PM
  5. Replies: 2
    Last Post: 05-22-2014, 06:11 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