Results 1 to 8 of 8
  1. #1
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46

    Between [start date] and [end date] won't work

    Hi,
    I've ready many posts before posting this question. Although there are other posting on this subject, no suggestions have worked so far. I've used this simple parameter criteria in queries before and can't figure out why it won't work now. The query is based on a select query with some expressions and a table. The field in question is the Expiration date field. I used the BETWEEN [Start Date] AND [End Date] in the criteria area. When I choose 1/1/12 as the start date and 8/9/12 as the end date, it returns records with the expiration dates of 7/11/14, 2/1/12, 1/18/15, and 1/25/16. It is only returning the correct month and day, not year. I have formatted this as general date, format([Expiration Date],"mmddyyyy"), you name it. This is my Sql for the query:

    Code:
    SELECT Contracts.Contract_ID, Contracts.Contract_Status, Contracts.Primary_Vendor_Name, Contracts.Start_Date, Contracts.Contract_Type, Contracts.Contract_Category, Contracts.Description, Contracts.Neg_Contract_Value, Contracts.Initial_Contract_Value, Contracts.COE, Contracts.[Legal Entity], Contracts.Sub_cat, Contracts.[Multi-Party], Contracts.Original_ext_party, Contracts.[Term Type], Contracts.Effective_Date, Contracts.Initial_term_months, Contracts.[Auto-Renewal_Term], Contracts.Auto_Renewal_Notification_Period, Contracts.Renewed, Contracts.Termination_Convenience, Contracts.Termination_Notice_Req, Calcs.[Termination Notice], Calcs.[Renewal Notice], Calcs.[Expiration Date]
    FROM Calcs INNER JOIN Contracts ON Calcs.Contract_ID = Contracts.Contract_ID
    WHERE (((Calcs.[Expiration Date]) Between [Start Date] And [End Date]));
    Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,116
    The expiration date field isn't text is it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46
    No its not. It is general date format in the query and date format in the table. However, in the calculation query that that field is based on is a dateadd function that adds a field that contains a number of days e.g. 30 to a field that contains a date. the field with 30 is formatted as number and the the date field is formatted as general date. This is then used in the RenewDates qry that won't work.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,116
    Can you post the db here? It sounds like something is causing the date to be seen as text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46
    Sure. I've stripped it down to include only the tables/queries/forms involved.
    Attached Files Attached Files

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,116
    Odd, but maybe it's a price of using the brackets method of gathering criteria. I always use a form, as it gives you more control. Here's your db with a form for the criteria, and it appears to work as expected.
    Attached Files Attached Files
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46
    I really appreciate this. Believe it or not, i did create a form to use for the dates but it wouldn't work. Again, you've made my life easier.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,116
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 12
    Last Post: 04-26-2012, 04:01 AM
  2. Replies: 15
    Last Post: 04-06-2012, 10:57 AM
  3. Replies: 5
    Last Post: 02-06-2011, 04:32 PM
  4. Replies: 1
    Last Post: 07-07-2010, 04:22 PM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 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