Results 1 to 3 of 3
  1. #1
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228

    in between dates pulling all years

    hi guys, i have a query that pulls info from two tables.


    i searched this issue but the 2 solutions i found did not work for me.

    i have a Between And criteria on the date field; it pulls the effective month/day range, but it also pulls all of the years.

    so instead of having records for only 4/1/12 and 4/25/12, i get records from all of the years. 4/1/1990 - 4/25/2012.

    this is my query:

    SELECT CDate(Format([PLAT_DATE],"mm/dd/yyyy")) AS Expr1, DIOD_DTA.PART_NUM, DIOD_DTA.LOT_NUM, PLATINUM.PLAT_TEMP, PLATINUM.PLAT_DOP, PLATINUM.PLAT_DOPANT_LOT, DIOD_DTA.TRR_AVE, DIOD_DTA.PIV_AVE, DIOD_DTA.IR_AVE, DIOD_DTA.PERC_PIV, DIOD_DTA.PERC_IR, DIOD_DTA.PERC_VF, DIOD_DTA.PERC_TRR, DIOD_DTA.NUM_TESTED, DIOD_DTA.VF_AVE
    FROM DIOD_DTA INNER JOIN PLATINUM ON DIOD_DTA.LOT_NUM = PLATINUM.LOT_NUM
    WHERE (((CDate(Format([PLAT_DATE],"mm/dd/yyyy"))) Between [Forms]![frm_SelectInfo]![txtStart] And [Forms]![frm_SelectInfo]![txtEnd]))
    ORDER BY PLATINUM.PLAT_DOP DESC;


    i have used this criteria many times, i'm not sure what the problem is.

  2. #2
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    if i Cdate out my cdate format function it works just fine

  3. #3
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    unfortunately, this will prevent the last date from being queried. if i choose 4/1/2012 and 4/25/2012 it will only give me up to 24, this is because of the time component i believe.

    if i add +1 to my between and statement, it gives me the expression is incorrectly or too complex to be evaluated error.

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

Similar Threads

  1. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  2. Replies: 1
    Last Post: 06-09-2011, 09:15 AM
  3. Populating 10+ Years of Dates Table
    By Mordred in forum Access
    Replies: 2
    Last Post: 04-27-2011, 06:40 PM
  4. Select changes in the years
    By acs_one in forum Queries
    Replies: 8
    Last Post: 11-27-2010, 05:26 PM
  5. Replies: 0
    Last Post: 07-27-2009, 07:51 AM

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