Results 1 to 5 of 5
  1. #1
    dgrimes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    11

    Need to show all records and only those in one date field that are above the current date

    I have a simple query and I cannot seem to figure out how to get it to show what I want.



    All records are currently shown. Some records have a date in a certain field and some don't. I need to have all records shown regardless and out of those, only show the records with dates in those fields, where the date is greater than the current date. Currently there can be multiple records because of numerous dates. I only want duplicate records to show if the date in that field is greater than the current date.

    I am sure this is an easy criteria but I just cannot figure it out.

    Any help would be greatly appreciated.

    Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the query:

    [MyDate]
    criteria: > Date()

  3. #3
    dgrimes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    11
    Quote Originally Posted by ranman256 View Post
    in the query:

    [MyDate]
    criteria: > Date()
    I tried that. That only makes the query show ONLY records that are greater than the current date. I need it to include all records, whether or not they have a date. If a date field is null it still needs to show. But a date that is less than the current date should not show.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You need to use an expression in the query, to show the date value if it is greater than the current date and blank otherwise.

    You can use the Iif function to accomplish that, something like:

    Iif([Mydate] > Date(), [Mydate], " ")

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I need it to include all records, whether or not they have a date. If a date field is null it still needs to show. But a date that is less than the current date should not show.
    I read this to mean you want all records where a date field is null AND all records where the date field is greater than the current date.

    I used a test dB and came up with:
    Code:
    SELECT fDates.FFYBeginDate, fDates.FFYEndDate
    FROM fDates
    WHERE (fDates.FFYEndDate > Date()) OR (fDates.FFYEndDate Is Null);
    FFYEndDate MUST be a Date/Time field!

    Change table.fieldnames to your names.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-15-2016, 11:46 AM
  2. Replies: 3
    Last Post: 08-21-2015, 08:14 AM
  3. Replies: 6
    Last Post: 12-11-2012, 09:40 PM
  4. Aslways show Current Date even in Old records
    By farhanahmed in forum Programming
    Replies: 3
    Last Post: 04-03-2011, 12:56 PM
  5. VB coding to show current date n time in access form
    By cwwaicw311 in forum Programming
    Replies: 6
    Last Post: 02-10-2010, 09:53 PM

Tags for this Thread

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