 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008parameter query for greater than date
I'd like a query to return results greater than six weeks from whatever date I enter (or preferably the date I am running the query). Right now I'm manually editing the query every time I run it to say ">#04/14/2008# but I know Access has to have a way to automate this. Thanks! For 6 weeks from today use --- >DateAdd("ww",6,Date()) For 6 weeks from date you enter use --- >DateAdd("ww",6,CVDate([Enter start date for search]))  SignatureKARL DEWEY Build a little - Test a little > I'd like a query to return results greater than six weeks from whatever date > I enter (or preferably the date I am running the query). Right now I'm > manually editing the query every time I run it to say ">#04/14/2008# but I > know Access has to have a way to automate this. Thanks! That didn't seem to work, could you explain what each piece of that code is doing? What does the "ww" do? > For 6 weeks from today use --- > >DateAdd("ww",6,Date()) [quoted text clipped - 6 lines] > > manually editing the query every time I run it to say ">#04/14/2008# but I > > know Access has to have a way to automate this. Thanks! >>What does the "ww" do? It is used in date functions like 'd', 'm', or 'q' for day of month, month of year, and quarter of year. "w" will give you day of week whereas 'ww' is week of the year. In this case it is adding 6 weeks. Did you place one of there in the criteria row of the query design view grid? That is where you would use them. SignatureKARL DEWEY Build a little - Test a little > That didn't seem to work, could you explain what each piece of that code is > doing? What does the "ww" do? [quoted text clipped - 9 lines] > > > manually editing the query every time I run it to say ">#04/14/2008# but I > > > know Access has to have a way to automate this. Thanks! I actually have two date fields I was going to use this with in the same query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing. When I enter the criteria manually as ">#4/17/2008#" it returns what I want. Any ideas? > >>What does the "ww" do? > It is used in date functions like 'd', 'm', or 'q' for day of month, month [quoted text clipped - 17 lines] > > > > manually editing the query every time I run it to say ">#04/14/2008# but I > > > > know Access has to have a way to automate this. Thanks! >>when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing. Your orignal post said "I'd like a query to return results greater than six weeks from whatever date I enter (or preferably the date I am running the query)." Your entry of >#04/14/2008# is not six weeks from 'date of entry' but 6 weeks ago, big difference. If you use the second one I posted then it will be from the date you enter. By the way if you use >#04/14/2008# then it will not pull a record dated #04/14/2008 as it is not greater. You might want to use >=#04/14/2008# for greater than OR equal. SignatureKARL DEWEY Build a little - Test a little > I actually have two date fields I was going to use this with in the same > query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns [quoted text clipped - 24 lines] > > > > > manually editing the query every time I run it to say ">#04/14/2008# but I > > > > > know Access has to have a way to automate this. Thanks! Ideally I'd like it to return any record with a date after 6 weeks prior to the date I am running the query. So if I run the query today, it would return anything after 4/17/08. I'm not too worried about the greater than or equal part. > >>when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing. > Your orignal post said "I'd like a query to return results greater than six [quoted text clipped - 37 lines] > > > > > > manually editing the query every time I run it to say ">#04/14/2008# but I > > > > > > know Access has to have a way to automate this. Thanks! So use -- >=DateAdd("ww",-6,Date()) This subtracts 6 weeks from today and then pulls all records after that. SignatureKARL DEWEY Build a little - Test a little > Ideally I'd like it to return any record with a date after 6 weeks prior to > the date I am running the query. So if I run the query today, it would [quoted text clipped - 42 lines] > > > > > > > manually editing the query every time I run it to say ">#04/14/2008# but I > > > > > > > know Access has to have a way to automate this. Thanks! Try using a negative number in the expression if you want to get information that is in the past. The expression DateAdd("ww",-6,Date()) will return a date that was 6 weeks PRIOR to today's date. '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== > Ideally I'd like it to return any record with a date after 6 weeks prior to > the date I am running the query. So if I run the query today, it would [quoted text clipped - 42 lines] >>>>>>> manually editing the query every time I run it to say ">#04/14/2008# but I >>>>>>> know Access has to have a way to automate this. Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.