Trying to return a range of results from access with just one criteria

I am trying to do a Microsoft Access 2013 query to return 7 days or results given just the first day. So the user will put in the date for Sunday and the query will return results for Sunday to Saturday.

I know I can have them do start date and end date but that seems excessive when they will always be looking for results for a specific range given the first date of the range.
If there is a way to even just query by a week somehow like "i want the 48th week of 2015" then that will work.

Any help is appreciated.
GjdaltonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't know that you could do that directly in a query without a lot of extra baggage, but in most cases when I want users to view a particular set of data, I'll create a small form that allows them to select the criteria, and then call my Form/Report from there. So if I have a report named "rSales", I'd create a form with a couple of Date fields, and I'd default those Date fields on opening to the current date (for my Start date) and +7 days for the "End" date.

I'd include a button that would run this code:

Dim sFilter as String
If Len(Me.txtStartDate)>0 Then
  If IsDate(Me.txtStartDate) Then
    sFilter = "YourDateField >=#" & Me.txtStartDate & "#"
  End If
End If

If Len(Me.txtEndDate) > 0 Then
  If IsDate(Me.txtEndDate) Then
    If Len(sFilter) > 0 Then
      sFilter = sFilter & " AND "
    End If
    sFilter = sFilter & " YourDateField <=#" & Me.txtEndDate & "#"
  End If
End If

'/ now open the report:
DoCmd.OpenReport "rSales", acViewPreview, , sFilter

Of course, you'd have to be sure to change Table, Field and Control names to match your own.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
I agree with Scott regarding entering start and end dates on a form, and referencing those controls in your query.  but to simply do what you are looking for in a query, the syntax might look like:

Parameters [Enter week start date] datetime;
SELECT * FROM yourTable
WHERE [DateField] >= [Enter week start date]
AND [DateField] < DateAdd("d", 7, [Enter week start date])
0
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>
...as Scott and Dale have already answered your question directly...

Just some small notes here.
Within the context of your question, you are dealing with two different concepts.
Sunday to Saturday
i want the 48th week of 2015
How you define a "Week" will effect each of your points
Is week 7 days?
Is a week Sunday Through Saturday?, or Monday through Sunday (ISO-8601)?
A year can have a varying number of weeks, some years have 52 weeks, others may have 53
A "week" can span two years (a few days from the end of 2014, and a few days from the start of 2015)

JeffCoachman
0
GjdaltonAuthor Commented:
Thank you all. Great answers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.