getting date range from user-entered date

[Seren]

I have a form that is to submit panel count info for each job, date, and
shift. I have a text field set up for a user to enter the date that they
want to add input for. The client wants the user to have to check the list
for that week to make sure the data for a certain day has not already been
entered. *Then* the user can click on an add record button, which pops up a
the field to add the panel count before submitting. *All* of the fields then
are added to a result table.

When the record is submitted, it needs to have the date with it. However, to
first view the week, I need to figure out how to get the begin date and the
end date for the week containing the date that was entered so that the
subform shows results from the entire week. Everything on the form is in
good working order... except for the fact that it is only retreiving that day
instead of the week because I don't know how to do that

Any help is greatly appreciated, as always!

Thanks,
Seren

 

[Klatuu]

Here are a couple of functions that will give you what you need.
WeekStartDate returns the monday date of the week for the date entered and
WeekEndDate returns the friday date for the date entered.

Function WeekEndDate(BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the last date of the accounting week for the date entered)

WeekEndDate = DateAdd("d", vbFriday - DatePart("w", BaseDate), BaseDate)
End Function

Function WeekStartDate(BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the first date of the accounting week for the date entered)

WeekStartDate = DateAdd("d", vbMonday - DatePart("w", BaseDate), BaseDate)
End Function

 

[Seren]

Ok... my next question would be, then, how do you put that into a SQL
statement? Here's what I have right now:

"SELECT OpID,Operation,OpNum,OperationDetail,[date]," & _
"day,Shift,Shift_Units FROM tblResults WHERE" & _
"tblResults.OpID = " & cboOperation.Value & " AND tblResults.OpNum = " & _
cboOperationDetail.Value & " AND tblResults.Shift = " & cboShift.Value & _
" AND tblResults.date = " & txtDate.Value

thank you!