Query based on highest number or current week

[Ken]

In a form there area fields for the week number, StartDate, FinishDate
ec't, I would like to setup a query that will find the current weeks
data, I know I could use a date dif function but I would like to be
able to click a control button on the form to open the query showing
only the current weeks records without having to type two dates, so
basically I'm looking for a function that will select the current week
or the highest number from the WeekNumber column, I've checked the
help files and various newsgroups but still can't figure it out.

 

[Damon Heron]

Assuming you want the five day week beginning on Monday, use this to get
dates:
Private Sub Command1_Click()
Dim x As String
Dim mybegindate As Date
Dim myenddate As Date
x = (Weekday(Now(), vbMonday) - 1)
mybegindate = Date - x
myenddate = mybegindate + 5
Debug.Print mybegindate, myenddate
'rest of code for query.........................

End Sub

 

[Ken]

Thank you for your reply, I'm very new to Access (just started
learning) so could you tell me where I should put the code.
T I A

 

[Damon Heron]

You asked about a command button on the form to run a query. this is the
code behind the command button's click event. In properties for the button,
select the three dots to the right of the on click event to go to the VB
window and paste this code. This shows you the beginning and ending dates
in the immediate window. You might want to add a breakpoint so you can see
the code being executed. As to the query, I don't have enough information to
help you. Do you want it on the form, or a query results window, a report,
or ?? If you have already designed your query, then you might want to open
a report with this command button, and use the sql query as the source for
the report with the parameters in the sql set to mybegindate and
myenddate....

Something like "Select mytable.date, mytable.field1, mytable.field2 from
mytable where mytable.date >= mybegindate and <= myenddate"

Incidently, the routine returns dates Monday and Saturday, not Friday.
Change "mydate+5" to mydate +4 for M-F...

 

[Ken]

Sorted, thank you.