I am having trouble understanding how datepart works as selection criteria when it comes to Access queries and forms. Basically, I have a query that captures all of the volunteer's work schedules. What I want to do is allow the user to input a date and for the form to display all of the work schedules for the week in which the date falls. Here is the query:
SELECT DatePart("ww",[WorkDate],1,3) AS WeekNo, tbl_Work_Schedule.WorkDate, tbl_Work_Schedule.Volunteer_ID, tbl_Volunteers.LastName, tbl_Volunteers.FirstName, tbl_Work_Schedule.Work_Location, tbl_Work_Schedule.Hours_Worked, [LastName] & ", " & [FirstName] AS VolunteerName FROM tbl_Volunteers INNER JOIN tbl_Work_Schedule ON tbl_Volunteers.ID = tbl_Work_Schedule.Volunteer_ID WHERE (((DatePart("ww",[WorkDate],1,3))=DatePart("ww",[forms]![frm_Work_Schedules]![WeekDate],1,3))) ORDER BY tbl_Work_Schedule.WorkDate, [LastName] & ", " & [FirstName];
Interestingly, I can run the query outside of the form by entering a parameter for "WeekDate" when prompted and I get records. But the query doesn't display records when it is the record source for the form. Other than making the user input a date range, how can I get the form to display the records for the week? What am I doing wrong???
Kevin Robertson
@Reply 3 years ago
In the On Load event of the form add Me.Requery.
Don GregoryOP
@Reply 3 years ago
I ended up using a date range instead and that works out better for the end user, but I think you are right-- I forgot to use Me.Requery. Thank you!
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.