• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

How to create a query based on people who have a certain date in the next week

I would like to create a list of people who have an expiration date in the next week.  I know that seems easy.  But if someone can help me with this one I can probably do the rest.
0
lehi52
Asked:
lehi52
  • 3
1 Solution
 
FlysterCommented:
You can use this in the Criteria section of your query:

=DatePart("ww",Date())+1

This will show every date that is one week more than the day the query is run.

Flyster
0
 
lehi52Author Commented:
I must be doing something wrong,  Its not returning any names.  I have one person that meets that criteria and they are not showing up.  Here is what I did:

The three fields it is supposed to return are policy name,   WCF POL#, and Exp Date.  I put this =DatePart("ww",Date())+1  into the criteria under exp. date.
0
 
FlysterCommented:
Sorry, my error. That's what happens when you rush. It should be:

=DatePart("ww",[YourDateField]) = DatePart("ww",Date())+1

Just change [YourDateField] to the name of your date field in the query.
0
 
FlysterCommented:
Thanks for the points, but I just thought of one slight problem with the submitted solution. It will work for the first 51 weeks of the year, but not the last. Here's a better one that will work no matter what time of the year it is:

DateDiff("ww",Now(),[Day])=1

So when it gets to the end of December, it will still know that January is just 1 week away!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now