Results 1 to 2 of 2
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Changing Date time in Between Date() AND Date()+...

    I would like to be able to have a select list where I can change the variable in : ((Accreditation.Expires) Between Date()+150 And Date()+91));



    I can make the list easy enough.

    Days until expiration List:
    Days Start Stop
    30 0 30
    60 31 60
    90 61 90
    120 91................and so forth
    150
    180

    How would I set it up to make the list activate in the below query? Insert Column 2 into the Date()+ and Column 3 into the end date.

    Or do I need to have some sort of stop and start column in the query?


    Code:
    SELECT Field.Field_ID, Field.Field_Name, System.[System Name], Nomenclature.Nomenclature, Component.Component, Version.Version, Accreditation.[ACC_#], Accreditation.Type_ACC, Accreditation.Accred, Accreditation.Expires, Accreditation.Compliance, Main.Hold, Accreditation.FD_Compliant
    FROM Version RIGHT JOIN (System RIGHT JOIN (Nomenclature INNER JOIN (Field INNER JOIN (Component INNER JOIN (Accreditation INNER JOIN Main ON Accreditation.Accred_ID = Main.[ACC_#]) ON Component.Comp_ID = Main.Comp_ID) ON Field.Field_ID = Main.Field_ID) ON Nomenclature.Nomen_ID = Main.Nomen_ID) ON System.SYS_ID = Main.SYS_ID) ON Version.Vers_ID = Main.Vers_ID
    WHERE (((Field.Field_ID)<>15) AND ((System.[System Name])<>"Tactical Systems") AND ((Accreditation.Expires) Between Date()+150 And Date()+91));
    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    look at the dateadd function

    your WHERE clause would look something like

    WHERE Accreditation.Expires between dateadd("d", [forms]![formname]![fieldname1], date()) and dateadd("d", [forms]![formname]![fieldname2], date())

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

Similar Threads

  1. Replies: 11
    Last Post: 07-20-2014, 06:22 PM
  2. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 PM
  3. Replies: 1
    Last Post: 03-13-2014, 07:23 PM
  4. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  5. Changing date to date with time
    By mulchgirl in forum Reports
    Replies: 3
    Last Post: 08-28-2012, 02:36 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