467,151 Members | 1,171 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > query date range <= part 2

Post your question to a community of 467,151 developers. It's quick & easy.

Query date range <= Part 2

100+
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/answer...ery-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
Expand|Select|Wrap|Line Numbers
  1. SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1,
  2.        DateValue([TblCalendarS104].[EndDate]) AS Expr2,
  3.        TblCalendarS104.StartDate,
  4.        TblCalendarS104.EndDate
  5. FROM TblCalendarS104
  6. WHERE CDate(((DateValue(TblCalendarS104.StartDate))>=Forms!FrmCalendarMain!SfrCalS104.Form!txtmon)
  7.   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
Expand|Select|Wrap|Line Numbers
  1. qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon]
  2. qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun]
here is the code for the recordset
Expand|Select|Wrap|Line Numbers
  1. Dim strControlName As String
  2. Dim dbsEquipBook As DAO.Database
  3. Dim rstCheck As DAO.Recordset
  4. Dim qdf As DAO.QueryDef
  5. Dim BookedStartTime As Date
  6. Dim BookedEndTime As Date
  7. Dim MyDate, MyWeekDay As Date
  8. Dim FirstDay As Date
  9. Dim LastDay As Date
  10. Dim MondayForm As Date
  11.  
  12. 'works out the first and last day of the week according to the selected date from the calendar
  13.  
  14. MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate]
  15. MyWeekDay = Weekday(MyDate)
  16. FirstDay = MyDate - MyWeekDay + 2
  17. LastDay = MyDate - MyWeekDay + 8
  18.  
  19. 'writes the first and last date to form
  20.  
  21. [Forms]![FrmCalendarMain].[frmCalendar].SetFocus
  22. [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay
  23. [Forms]![FrmCalendarMain].[SfrCalS104]![TxtSun] = LastDay
  24.  
  25. Set dbsEquipBook = CurrentDb()
  26.     Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
  27.  
  28. qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon]
  29. qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun]
  30.  
  31. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  32.  
  33. ' loops through recordset this is where it is picking up the wrong records
  34.  
  35. Do While Not rstCheck.EOF
  36.  
  37.     BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
  38.     BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
  39.     BookedDay = Format(rstCheck!StartDate, "ddd")
  40.  
  41. 'handles midnight values
  42.  
  43.     If BookedEndTime = "00:00:00" Then
  44.        BookedEndTime = "23:59:59"
  45.     End If
  46.  
  47. 'this section loops through and changes colour of controls if criteria is met this represents the calendar bookings
  48.  
  49. For i = 8 To 23
  50.   For j = 0 To 1
  51.     If i < 10 Then
  52.       strControlName = "txt" & BookedDay & "0" & i & ":"
  53.     Else
  54.       strControlName = "txt" & BookedDay & i & ":"
  55.     End If
  56.     If j = 0 Then
  57.       strControlName = strControlName & "00"
  58.     Else
  59.       strControlName = strControlName & "30"
  60.     End If
  61.    If Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then
  62.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191)
  63.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191)
  64.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).Enabled = False
  65.     End If
  66.  
  67.   Next
  68. Next
  69.  
  70.  
  71.  rstCheck.MoveNext
  72. Loop
  73.  
  74. rstCheck.Close
  75.  
  76. End Sub
Any help is much appreciated

Regards Phill
Sep 13 '09 #1

✓ answered by 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.

  • viewed: 5480
Share:
19 Replies
ADezii
Expert 8TB
A Syntax change should do the trick:
Expand|Select|Wrap|Line Numbers
  1. qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
  2. qdf.Parameters(1).Value = Eval(qdf.Parameters(1).Name)
Sep 13 '09 #2
FishVal
Expert 2GB
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.
Sep 13 '09 #3
100+
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1, DateValue([TblCalendarS104].[EndDate]) AS Expr2, TblCalendarS104.StartDate, TblCalendarS104.EndDate 
  3. FROM TblCalendarS104 
  4. WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtsun])); 
  5.  
  6.  

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

Expand|Select|Wrap|Line Numbers
  1. Dim strControlName As String 
  2. Dim dbsEquipBook As DAO.Database 
  3. Dim rstCheck As DAO.Recordset 
  4. Dim qdf As DAO.QueryDef 
  5. Dim BookedStartTime As Date 
  6. Dim BookedEndTime As Date 
  7. Dim MyDate, MyWeekDay As Date 
  8. Dim FirstDay As Date 
  9. Dim LastDay As Date 
  10. Dim MondayForm As Date 
  11.  
  12. 'works out the first and last day of the week according to the selected date from the calendar 
  13.  
  14. MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate] 
  15. MyWeekDay = Weekday(MyDate) 
  16. FirstDay = MyDate - MyWeekDay + 2 
  17. LastDay = MyDate - MyWeekDay + 8 
  18.  
  19. 'writes the first and last date to form 
  20.  
  21. [Forms]![FrmCalendarMain].[frmCalendar].SetFocus 
  22. [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay 
  23. [Forms]![FrmCalendarMain].[SfrCalS104]![TxtSun] = LastDay 
  24.  
  25. Set dbsEquipBook = CurrentDb() 
  26.     Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck") 
  27.  
  28. qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon] 
  29. qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun] 
  30.  
  31. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly) 
  32.  
  33. ' loops through recordset that contains the query with the date range this is where it is picking up the wrong records 
  34.  
  35. Do While Not rstCheck.EOF 
  36.  
  37.     BookedStartTime = Format(rstCheck!StartDate, "hh:nn") 
  38.     BookedEndTime = Format(rstCheck!EndDate, "hh:nn") 
  39.     BookedDay = Format(rstCheck!StartDate, "ddd") 
  40.  
  41. 'handles midnight values 
  42.  
  43.     If BookedEndTime = "00:00:00" Then 
  44.        BookedEndTime = "23:59:59" 
  45.     End If 
  46.  
  47. '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 
  48.  
  49. For i = 8 To 23 
  50.   For j = 0 To 1 
  51.     If i < 10 Then 
  52.       strControlName = "txt" & BookedDay & "0" & i & ":" 
  53.     Else 
  54.       strControlName = "txt" & BookedDay & i & ":" 
  55.     End If 
  56.     If j = 0 Then 
  57.       strControlName = strControlName & "00" 
  58.     Else 
  59.       strControlName = strControlName & "30" 
  60.     End If 
  61.    If Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then 
  62.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191) 
  63.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191) 
  64.       Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).Enabled = False 
  65.     End If 
  66.  
  67.   Next 
  68. Next 
  69.  
  70.  
  71.  rstCheck.MoveNext 
  72. Loop 
  73.  
  74.  rstCheck.Close 
  75.  
  76.  End Sub 
  77.  

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
Sep 13 '09 #4
FishVal
Expert 2GB
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.
Sep 13 '09 #5
ADezii
Expert 8TB
The Parameters should work as demonstrated in Post #2.
Sep 13 '09 #6
100+
Hi Adezii

I tried those parameters and it gave the same result

Regards Phill
Sep 13 '09 #7
FishVal
Expert 2GB
Phill.

Would you like to attach sanitized copy of your db to the thread?
Sep 13 '09 #8
100+
Hi Fishval


yes how do i do that

Regards Phill
Sep 13 '09 #9
FishVal
Expert 2GB
  • 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].
Sep 13 '09 #10
100+
Thanks Fishval

Regards Phill
Attached Files
File Type: zip Database2.zip (293.4 KB, 86 views)
Sep 13 '09 #11
FishVal
Expert 2GB
A don't have Access 2007.
Could you convert it to Access 2003 mdb file?
Sep 13 '09 #12
100+
Try this

regards Phill
Attached Files
File Type: zip Database2003.zip (323.2 KB, 79 views)
Sep 13 '09 #13
FishVal
Expert 2GB
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.
Attached Files
File Type: zip Database2003.zip (274.2 KB, 103 views)
Sep 13 '09 #14
ADezii
Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strControlName As String
  2. Dim dbsEquipBook As DAO.Database
  3. Dim rstCheck As DAO.Recordset
  4. Dim qdf As DAO.QueryDef
  5. Dim BookedStartTime As Date
  6. Dim BookedEndTime As Date
  7. Dim MyDate, MyWeekDay As Date
  8. Dim FirstDay As Date
  9. Dim LastDay As Date
  10. Dim MondayForm As Date
  11. Dim frm As Form
  12. Dim ctl As Control
  13.  
  14. Set frm = [Forms]![FrmCalendarMain]
  15.  
  16. 'works out the first and last day of the week according
  17. 'to the selected date from the calendar
  18.  
  19. MyDate = frm.[frmCalendar]![txtDate]
  20. MyWeekDay = Weekday(MyDate)
  21. FirstDay = MyDate - MyWeekDay + 2
  22. LastDay = MyDate - MyWeekDay + 8
  23.  
  24. 'writes the first and last date to form
  25. frm.[frmCalendar].SetFocus
  26. frm![SfrCalS104]![TxtMon] = FirstDay
  27. frm![SfrCalS104]![TxtSun] = LastDay
  28.  
  29. Set dbsEquipBook = CurrentDb()
  30.     Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
  31.  
  32. qdf.Parameters("dteStart").Value = frm![SfrCalS104]![TxtMon]
  33. qdf.Parameters("dteEnd").Value = frm![SfrCalS104]![TxtSun]
  34.  
  35. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  36.  
  37. 'clear controls
  38.  
  39.     For lngweekday = 1 To 7
  40.         For i = 8 To 23
  41.             For j = 0 To 1
  42.               If i < 10 Then
  43.                 strControlName = "txt" & Format(lngweekday, "ddd") & "0" & i & ":"
  44.               Else
  45.                 strControlName = "txt" & Format(lngweekday, "ddd") & i & ":"
  46.               End If
  47.               If j = 0 Then
  48.                 strControlName = strControlName & "00"
  49.                 Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
  50.               Else
  51.                 strControlName = strControlName & "30"
  52.                 Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
  53.               End If
  54.               ctl.BackColor = 15527148
  55.               ctl.ForeColor = 15527148
  56.               ctl.Enabled = True
  57.             Next
  58.         Next
  59.     Next
  60.  
  61.  
  62. ' loops through recordset that contains the query with the date range
  63. 'this is where it is picking up the wrong records
  64.  
  65. Do While Not rstCheck.EOF
  66.   BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
  67.   BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
  68.   BookedDay = Format(rstCheck!StartDate, "ddd")
  69.  
  70. 'handles midnight values
  71.  
  72.     If BookedEndTime = "00:00:00" Then
  73.        BookedEndTime = "23:59:59"
  74.     End If
  75.  
  76. 'this section loops through and changes colour of controls which repesent a
  77. 'time slot on the form if criteria is met this represents the calendar bookings
  78. For i = 8 To 23
  79.   For j = 0 To 1
  80.     If i < 10 Then
  81.       strControlName = "txt" & BookedDay & "0" & i & ":"
  82.     Else
  83.       strControlName = "txt" & BookedDay & i & ":"
  84.     End If
  85.     If j = 0 Then
  86.       strControlName = strControlName & "00"
  87.       Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
  88.     Else
  89.       strControlName = strControlName & "30"
  90.       Set ctl = frm![SfrCalS104].Form.Controls(strControlName)
  91.     End If
  92.     If ctl >= BookedStartTime And ctl < BookedEndTime Then
  93.         ctl.BackColor = RGB(191, 191, 191)
  94.         ctl.ForeColor = RGB(191, 191, 191)
  95.         ctl.Enabled = False
  96.     Else
  97.         ctl.BackColor = 15527148
  98.         ctl.ForeColor = 15527148
  99.         ctl.Enabled = True
  100.     End If
  101.   Next
  102. Next
  103.  
  104.  
  105.  rstCheck.MoveNext
  106. Loop
  107.  
  108. rstCheck.Close
  109.  
Sep 14 '09 #15
100+
Hi guys

Thats brilliant thank you so much

Its very much appreciated

Regards Phill
Sep 14 '09 #16
FishVal
Expert 2GB
You are welcome, Phill.
BTW, the code could be further optimized if you like. So far it looks quite kludge.
Sep 14 '09 #17
100+
Hi Fishval

that would be great if you have time

I always like to improve my knowledge any tips are much appreciated

Regards Phill
Sep 14 '09 #18
ADezii
Expert 8TB
@FishVal
Hello FishVal, I checked my Dictionary of Computer Terms from top to bottom, but could not find any reference to quite kludge. (LOL).
Sep 14 '09 #19
FishVal
Expert 2GB
I've got it from science fiction story by John Varley "Press Enter" something about 15 years ago.
And, sure, wikipedia has something to say about the meaning of the word.
Sep 15 '09 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics
Access Query Date Range
1 post views Thread by Brian Jorgenson | last post: by
Date Range Prompt for a Query
2 posts views Thread by sixdeuce62 | last post: by
date range query
1 post views Thread by flumpuk | last post: by
BYTES.COM © 2021
About Us
Advertise
Terms Of Use
Privacy Policy
Manage Cookies
Contact Us
Sitemap
Microsoft Access / VBA Answers Sitemap
Microsoft Access / VBA Insights Sitemap

Follow us! Get the Latest Bytes Updates
By using this site, you agree to our Privacy Policy and Terms of Use.