Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Corect Criteria in a MS Access query

    I am entering a criteria in a query in MS Access 2010.

    The expression is as shown below:


    >#Date()# and <#Exp_Date#


    where Date() is current date

    and

    Exp_Date is the expiration date.

    In other words I am trying to find all licenses that have not expired. Their Exp_Date is greater than today current date.

    MS access 2010 says that the expressions contains in incorrect date value,;I have defined them as you see..

    What am I doing wrong?



    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou_Reed


    The

  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,518
    You would only use # with hard-coded dates. Try simply

    > Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I tried this:


    >Date() And <"Exp_Date"


    and I got an error date mismatch. It removed the original error, but added a new one "date mismatch".

    How do I correct this.

    Thanks in advance.


    Respectfully,


    Lou_Reed

  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,518
    Did you try what I suggested?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Yes,

    I dropped the # from around the Date() , so it went from this: #Date()# to this: Date().

    Now after I finished typing it in the MS access software put quotes around Date()so it looked like this "Date()".

    I will try it again.

    R,


    Lou_Reed

  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,518
    Confirm the data type of the Exp_Date field. If it's text, all bets are off. I assumed it was date/time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I will check. I hope that it is date/time, it should be. The question is even if it is date/time wheat units is it in. I assume that
    it is in days. So when I use an expression Date()+90 and it looking 90 days into the future.

    Date()+ 90 days.

    I will check my design view records.

    R,

    Lou_Reed

  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,518
    A date/time field is stored as a double, so you can do that kind of math on it:

    ?cdbl(now())
    42513.4134837963
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    My Exp_Date is date/time as I expected. I still get a data mismatch. My only guest is it need
    to match unit for ExP_Date and 90.

    How do I do that?


    Respectfully,


    Lou_Reed

    PS Sorry this I s taking so long. I am not new to MS Access 2010, but I am new to MS Access 2013.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here? If it's a date/time field, it shouldn't wrap Date() in quotes when you finish typing it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    The zip file is attached. I again am not sure what is happening.

    Any help appreciated. Thanks in advance.


    R,

    Lo_Reed
    Attached Files Attached Files

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure why you try to add the field to itself. This works:

    SELECT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblPropPass.PropPass_ID, tblPropPass.Exp_Date
    FROM tblPersonnel INNER JOIN tblPropPass ON tblPersonnel.[Personnel_ID] = tblPropPass.[Personnel_ID]
    WHERE (((tblPropPass.Exp_Date)>Date()));

    The other one you didn't have the > sign:

    SELECT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblPropPass.PropPass_ID, tblPropPass.Exp_Date
    FROM tblPersonnel INNER JOIN tblPropPass ON tblPersonnel.[Personnel_ID] = tblPropPass.[Personnel_ID]
    WHERE (((tblPropPass.Exp_Date)>Date()+90));
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I see your SQL code. I was hoping to use just the query designer. Again, when I add 90 days to
    date how do I tell it is 90 days, not 90 hours, minutes, months, etc.

  14. #14
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    SELECT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblPropPass.PropPass_ID, tblPropPass.Exp_Date
    FROM tblPersonnel INNER JOIN tblPropPass ON tblPersonnel.[Personnel_ID] = tblPropPass.[Personnel_ID]
    WHERE (((tblPropPass.Exp_Date)>Date()+90));

    I believe the above code has an error. This code will tell your the property passes that will not expire in the next 90 days. I wanted the ones that will expire.

    I think my problem was that I used Exp_Date, instead tblPropPass.Exp_Date.

    When I make that correction all is okay.

    Any way I just wanted to point out the error to you.

    Thanks for your help.

    R,


    Lou_Reed

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like I said, dates are stored as doubles, so

    Date()+90

    adds 90 days. You can be more explicit and use the DateAdd() function, where you can specify the interval. As to the "error", just flip the operator. I was simply pointing out that you didn't have one in there at all. As to using the query designer, you certainly can use it. You get an error because you've got

    >"Exp_Date"

    instead of

    >Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-03-2015, 08:56 AM
  2. Replies: 3
    Last Post: 09-16-2015, 04:38 PM
  3. Replies: 7
    Last Post: 04-30-2014, 06:55 AM
  4. query criteria in access 2007
    By jeskit in forum Access
    Replies: 2
    Last Post: 11-23-2011, 06:44 AM
  5. Excel not using Access query criteria
    By thart21 in forum Queries
    Replies: 10
    Last Post: 04-30-2010, 09:58 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