Returning Days Within Date Range Based on Day of the Week 1

[LincolnMarkV]

Hello friends,

I am working on a scheduling database and have run into a snag. I have a form which has both a start date and an end date text box. I also have 7 checkboxs that represent each day of the week. When I check a checkbox, it will write the day of the week in a hidden textbox. I use the data in the hidden textboxes to supply the criteria to a listbox query that shows the instructor's existing schedule based on the date range and the days of the week selected. This all works perfectly.

What I am looking to do now is to return all of the days of the week that fall between the date range and the specified day(s) of the week. For example:

frmCorpSchedule (This is my form)
txtStartDate = 7/1/09
txtEndDate = 7/8/09
chkMonday = true
chkTuesday = true
txtMonday = "Monday"
txtTuesday = "Tuesday"

My query should return:
7/6/09
7/7/09

Please advise on how I would accomplish this using a query.

After I have the dates, I need to compare them to the instructor's existing schedule to ensure there are no conflicts.

Thanks!!!

 

[dhookom]

My query should return:" what is the base table of your query? What is in the "FROM " clause? Where are you hoping to display the query?

I would rename all the check boxes to something like
chk1 for Sunday, chk2 for Monday,...

Generically, I would build a table of all dates and use code to write SQL:

Dim strSQL as String
Dim strIn as String
Dim intDOW as Integer
strSQL = "SELECT TheDateField FROM tblAllDates "
For intDOW = 1 to 7
   If Me("chk" & intDOW) <> 0 Then
      strIn = strIn & intDOW & ", "
   End If
Next
strSQL = strSQL & " WHERE TheDateField BETWEEN # & _
   Me.txtStartDate & "# AND #" & Me.txtEndDate & "# "
If Len(strIn) > 0 Then
   strSQL = strSQL & " AND Weekday(TheDateField) IN (" & _
      strIN & "9) "
End If
[green]' now I have the query but don't know what to do with it[/green]

Duane
Hook'D on Access
MS Access MVP

 

[LincolnMarkV]

Thanks Duane.

Where I went wrong is that I didn't have an actual table of dates. I created a table of everyday for the next 50 years. I got the query working as I had hoped, but I will test your way as it looks much cleaner.

I have also solved my next step problem.