Query date range <= Part 2

[phill86]

Hi

I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread.

here is the original tread

http://bytes.com/topic/access/answers/872005-query-date-range

Just to clarify what I am trying to achieve....

I have a table with calendar booking records which has been imported from outlook so the start date and end date are in this format dd:mm:yyyy hh:nn:ss this is what I believe is the root of the problem

I have a form that is designed to display the calendar bookings from the table in a monday - sunday format so a user selects a date and on the form the full week is displayed so for example if the user selects 9/9/09 the form will display from the 7/9/09 - 13/9/09 similar to how outlook works

The query that I was having problems with is designed to pick up the start and end date range on that form and return all the calendar bookings from the table within the date range I have now got this to work how I want it to

SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1,
       DateValue([TblCalendarS104].[EndDate]) AS Expr2,
       TblCalendarS104.StartDate,
       TblCalendarS104.EndDate
FROM TblCalendarS104
WHERE CDate(((DateValue(TblCalendarS104.StartDate))>=Forms!FrmCalendarMain!SfrCalS104.Form!txtmon)
  AND CDate((DateValue(TblCalendarS104.EndDate))<=Forms!FrmCalendarMain!SfrCalS104.Form!txtsun));

The problem I am now having is that the recordset is not picking up the correct records from the query but if you run the query by itself it displays the correct records

I suspect it has something to do with the following two lines of code

qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon]
qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun]

here is the code for the recordset

Dim strControlName As String
Dim dbsEquipBook As DAO.Database
Dim rstCheck As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BookedStartTime As Date
Dim BookedEndTime As Date
Dim MyDate, MyWeekDay As Date
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date

'works out the first and last day of the week according to the selected date from the calendar

MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate]
MyWeekDay = Weekday(MyDate)
FirstDay = MyDate - MyWeekDay + 2
LastDay = MyDate - MyWeekDay + 8

'writes the first and last date to form

[Forms]![FrmCalendarMain].[frmCalendar].SetFocus
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtSun] = LastDay

Set dbsEquipBook = CurrentDb()
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon]
qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun]

Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)

' loops through recordset this is where it is picking up the wrong records

Do While Not rstCheck.EOF

    BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
    BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
    BookedDay = Format(rstCheck!StartDate, "ddd")

'handles midnight values
    
    If BookedEndTime = "00:00:00" Then
       BookedEndTime = "23:59:59"
    End If

'this section loops through and changes colour of controls if criteria is met this represents the calendar bookings

For i = 8 To 23
  For j = 0 To 1
    If i < 10 Then
      strControlName = "txt" & BookedDay & "0" & i & ":"
    Else
      strControlName = "txt" & BookedDay & i & ":"
    End If
    If j = 0 Then
      strControlName = strControlName & "00"
    Else
      strControlName = strControlName & "30"
    End If
   If Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191)
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191)
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).Enabled = False
    End If
    
  Next
Next


 rstCheck.MoveNext
Loop
 
rstCheck.Close
 
End Sub

Any help is much appreciated

Regards Phill

[ADezii]

A Syntax change should do the trick:

qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Parameters(1).Value = Eval(qdf.Parameters(1).Name)

[FishVal]

Dear Phil,

Eventually I've managed to look through your code and, to tell the truth, I'm somewhat lost as for what it is supposed to do.

• Part of the code determining week bounds is somewhat uncertain. When entered date is Sunday it is expected to give the next week. Is it what it is supposed to do?
• You query is expected to return only those records which both startdate and endate are within a given week. Is it what it is supposed to do?
• The section of code which changes controls appearance is a total mystery. From what I've understood it change appearance of controls which have in their names signature of weekday taken from startdate and hours between time portions of startdate and enddate. Again, is it what it is supposed to do?

Regards,
Fish.

[phill86]

Hi Guys

Thanks for getting back to me

Sorry for any confusion caused

OK let me try again

I have "re-created" the look of an outlook calendar in my form I want to represent the bookings on that form just like an outlook calendar booking.I have used unbound text boxes to represent the time slots in the columns of the calendar and I change the colour of these to represent a calendar booking

These outlook bookings have been imported from outlook into my access table.

humour me for a second and open an outlook calendar and select the 3/9/09 on the small calendar control on the left.. outlook diplays the dates 31/8/09 to the 06/9/09 (mon-sun) on the right hand side in columns representing each day of the week

This is exactly how my form works

all i need to do is write a query that takes the first date the 31/8/09 (Mon) and the last date 06/9/09 (sun) and search for bookings within that date range i.e the start and end date of the week that is displayed

these dates have been worked out from the users selection on the small calendar control on the left hand side ie the 03/9/09 the code then works out the date for monday (31/08/09) and the sunday (06/09/09) and written these dates to the form in the fields [txtmon] and [txtsun]

the query below works when run by itself

SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1, DateValue([TblCalendarS104].[EndDate]) AS Expr2, TblCalendarS104.StartDate, TblCalendarS104.EndDate 
FROM TblCalendarS104 
WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtsun]));

so for example I have the following "calendar bookings" in my table

Location Start date End Date

Room1 31/08/2009 09:00:00 31/08/2009 12:00:00
Room1 01/09/2009 13:00:00 01/09/2009 14:00:00
Room1 03/09/2009 17:00:00 03/09/2009 21:00:00
Room1 06/09/2009 17:00:00 06/09/2009 21:00:00

the query finds these records and works fine but when the code runs it does not recognise that there are any records in the recordset and closes the recordset

This is just one example I get various different anomalies picking up extra dates etc..

but if I run the recordset code on the the date range 14/9/09 - 20/9/09 it works fine

Here is the code for the recordset

Dim strControlName As String 
Dim dbsEquipBook As DAO.Database 
Dim rstCheck As DAO.Recordset 
Dim qdf As DAO.QueryDef 
Dim BookedStartTime As Date 
Dim BookedEndTime As Date 
Dim MyDate, MyWeekDay As Date 
Dim FirstDay As Date 
Dim LastDay As Date 
Dim MondayForm As Date 
  
'works out the first and last day of the week according to the selected date from the calendar 
  
MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate] 
MyWeekDay = Weekday(MyDate) 
FirstDay = MyDate - MyWeekDay + 2 
LastDay = MyDate - MyWeekDay + 8 
  
'writes the first and last date to form 
  
[Forms]![FrmCalendarMain].[frmCalendar].SetFocus 
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay 
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtSun] = LastDay 
  
Set dbsEquipBook = CurrentDb() 
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck") 
  
qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon] 
qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun] 
  
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly) 
  
' loops through recordset that contains the query with the date range this is where it is picking up the wrong records 
  
Do While Not rstCheck.EOF 
  
    BookedStartTime = Format(rstCheck!StartDate, "hh:nn") 
    BookedEndTime = Format(rstCheck!EndDate, "hh:nn") 
    BookedDay = Format(rstCheck!StartDate, "ddd") 
  
'handles midnight values 
  
    If BookedEndTime = "00:00:00" Then 
       BookedEndTime = "23:59:59" 
    End If 
  
'this section loops through and changes colour of controls which repesent a time slot on the form if criteria is met this represents the calendar bookings 
  
For i = 8 To 23 
  For j = 0 To 1 
    If i < 10 Then 
      strControlName = "txt" & BookedDay & "0" & i & ":" 
    Else 
      strControlName = "txt" & BookedDay & i & ":" 
    End If 
    If j = 0 Then 
      strControlName = strControlName & "00" 
    Else 
      strControlName = strControlName & "30" 
    End If 
   If Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then 
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191) 
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191) 
      Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).Enabled = False 
    End If 
  
  Next 
Next 
  
  
 rstCheck.MoveNext 
Loop 
  
 rstCheck.Close 
  
 End Sub

I hope I have made myself clear and once again apologies for any confusion

I really appreciate you taking the time to help me

Regards Phill

[FishVal]

Ok.

With only one-day booking required this makes sense.

Well.

Being on your place, I would drop this parameters and tried dynamic SQL statement creation with explicit date constants. If you want to go this way, then pay attention to using proper delimiters and date constant format.

[ADezii]

The Parameters should work as demonstrated in Post #2.

[phill86]

Hi Adezii

I tried those parameters and it gave the same result

Regards Phill

[FishVal]

Phill.

Would you like to attach sanitized copy of your db to the thread?

[phill86]

Hi Fishval


yes how do i do that

Regards Phill

[FishVal]

• Remove all sensitive and/or not related to the problem data and objects.
• Compact and repair.
• Make sure the problem persists.
• Zip database file.
• When editing post where should be button [Manage attachments].

[phill86]

Thanks Fishval

Regards Phill

[FishVal]

A don't have Access 2007.
Could you convert it to Access 2003 mdb file?

[phill86]

Try this

regards Phill

[FishVal]

Seems working now.

I've added parameters declaration to the query and removed from the query direct references to form's controls. Also, I've added code portion which clears controls before rendering new dataset.

[ADezii]

Besides FishVal's excellent advice, I feel as though you can significantly cut down the amount of code, and increase its efficiency and readability by referring to Form and Control Objects explicitly. I am referring to Lines 11, 12, 14, 25 to 27, 49, 54 to 56, 87, 90, 93 to 95, etc.

Dim strControlName As String
Dim dbsEquipBook As DAO.Database
Dim rstCheck As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BookedStartTime As Date
Dim BookedEndTime As Date
Dim MyDate, MyWeekDay As Date
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date
Dim frm As Form
Dim ctl As Control

Set frm = [Forms]![FrmCalendarMain]

'works out the first and last day of the week according
'to the selected date from the calendar

MyDate = frm.[frmCalendar]![txtDate]
MyWeekDay = Weekday(MyDate)
FirstDay = MyDate - MyWeekDay + 2
LastDay = MyDate - MyWeekDay + 8

'writes the first and last date to form
frm.[frmCalendar].SetFocus
frm![SfrCalS104]![TxtMon] = FirstDay
frm![SfrCalS104]![TxtSun] = LastDay

Set dbsEquipBook = CurrentDb()
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters("dteStart").Value = frm![SfrCalS104]![TxtMon]
qdf.Parameters("dteEnd").Value = frm![SfrCalS104]![TxtSun]

Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)

'clear controls

    For lngweekday = 1 To 7
        For i = 8 To 23
            For j = 0 To 1
              If i < 10 Then
                strControlName = "txt" & Format(lngweekday, "ddd") & "0" & i & ":"
              Else
                strControlName = "txt" & Format(lngweekday, "ddd") & i & ":"
              End If
              If j = 0 Then
                strControlName = strControlName & "00"
                Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
              Else
                strControlName = strControlName & "30"
                Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
              End If
              ctl.BackColor = 15527148
              ctl.ForeColor = 15527148
              ctl.Enabled = True
            Next
        Next
    Next


' loops through recordset that contains the query with the date range
'this is where it is picking up the wrong records

Do While Not rstCheck.EOF
  BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
  BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
  BookedDay = Format(rstCheck!StartDate, "ddd")

'handles midnight values
    
    If BookedEndTime = "00:00:00" Then
       BookedEndTime = "23:59:59"
    End If

'this section loops through and changes colour of controls which repesent a
'time slot on the form if criteria is met this represents the calendar bookings
For i = 8 To 23
  For j = 0 To 1
    If i < 10 Then
      strControlName = "txt" & BookedDay & "0" & i & ":"
    Else
      strControlName = "txt" & BookedDay & i & ":"
    End If
    If j = 0 Then
      strControlName = strControlName & "00"
      Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
    Else
      strControlName = strControlName & "30"
      Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
    End If
    If ctl >= BookedStartTime And ctl < BookedEndTime Then
        ctl.BackColor = RGB(191, 191, 191)
        ctl.ForeColor = RGB(191, 191, 191)
        ctl.Enabled = False
    Else
        ctl.BackColor = 15527148
        ctl.ForeColor = 15527148
        ctl.Enabled = True
    End If
  Next
Next


 rstCheck.MoveNext
Loop
 
rstCheck.Close