Date help

I feel like this is one I should be able to figure out, but I keep drawing a blank. In my DB I have a query that has a date field. I'd like to create a macro or criteria within the query that, when run, returns only those records that fall within the previous Sunday through Saturday period. In otherwords, if it's Tuesday, for example, it will return all records from Sunday up to the previous Saturday. Any ideas? I keep fiddling around with date function and Instr functions, but I think I'm going around in circles.
Thanks.
Jim

What you can see below is a function that returns the Monday's date of a week in which a given date is.



For example, if dtmDate is 13/01/2004 then it will return 12/01/2004.

You can play with this function to return any day's date for you.

Thanks, but if I'm reading it correctly, the dtmDate variable still has to "fed" a date that is a starting point for the function. So as every day that passes the outcome is one date later, which won't be what I need.
What about a parameter query that asks for a week number, which would be inserted into a funtion that returns the appropriate Sunday-Saturday dates? For example, user puts in 1 for the week number and the query returns all records with dates Jan 4th - Jan 10th. Any ideas?
Thanks again.
Jim

Use DatePart function to return the week number where specified date is.
DatePart returns a Variant (Integer) containing the specified part of a given date.
If you wish to return the week number then you need to use "ww" as the first actual parameter.

Then you can select the records returning a given week number:

dim MySQL as string
MySQL="select * from tblTable where datepart("ww",DateField_in_tblTable,vbusesystem,vbusesystem)=" & val(inputbox("Enter a week number: "))
docmd.runsql MySQL

Check Help for more about DatePart function.

Thanks again, I'm assuming that this needs to be put into the VBA code (I did it as a click event to a cmd button to get it working), but Access keeps throwing up an error (Expect end of statement) highlighting words after quotes when clicking off that line of code. I can't use variables and such in SQL view of queries, right? Or as report data source? I'm thinking even if I get this working, the user clicks the button, input box pops up, number goes in, and a data sheet view of the results shows. Wouldn't this have to be saved as a query somewhere to design a report for it? The goal is to have a button, that when clicked, prints a Sunday-Saturday time and attendance records for the week choosen.
The more I think of this the more I think my boss will just have to use a "dual parameter" query (beginning date and ending date).

See the sample database I designed for you.

Open frmButton and click the button. It brings up an input box that prompts the user for a week number. When the user clicks OK, the subform will show only the records with date in that week.

If you wish to execute the code step by step then place a breakpoint to the OnClick event and press F8 to step to the next statement.

Play with it, investigate. I am sure you'll find your way.

Thanks, I don't know why it didn't work before, unless I didn't see a typo. I've been messing around with it for a couple of hours, trying to use your example to feed a report, but the date and names fields come up with "#Name?". Here's what I've got...

Private Sub btnWeekly_Click()
Dim MySQL As String
Dim stDocName As String

stDocName = "rptWeeklyTA"

MySQL = "SELECT tblEmployeeInfo.EmpID, tblEmployeeInfo.LastName, tblEmployeeInfo.FirstName, tblViolation.Date, tblViolation.Violation, tblViolation.[T/A] FROM tblEmployeeInfo INNER JOIN tblViolation ON tblEmployeeInfo.EmpID = tblViolation.EmpID where DatePart('ww',tblViolation.Date,0,0)=" & Val(InputBox("Enter a week number:"))

'rptWeeklyTA.Report.RecordSource = MySQL
DoCmd.OpenReport stDocName, acPreview

'sfrmAllMIFEquipment1.Form.Requery
End Sub

When I run the line above the DoCmd not as a comment, it throws up a 424 error. I'm getting close, I think, the report tries to open. I even changed the control source of the text boxes to tblName.FieldName format, but didn't make a difference. Thanks again for the input.
Jim

It's something with names in your report. I mean the field names where the controls of your report refer to.

Post your DB, but compress and zip it before posting. I will have a look at it.

Wouldn't attach. PM'd you with it's location.... Hope it works.

Ok, hopefully I didn't get rid of anything you need. The T/A Violations form has a button labeled Weekly T/A. That's the one I'm fighting with. In the VBA code, I forgot to undo the experimentations, like brackets around the field names and the "DoCmd.openQuery..." line. Once those are back/gone, the report opens with what appears as no record source. I think it's missing a simple line of code.
Jim