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 - 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
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.
19 5972
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)
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.
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
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.
The Parameters should work as demonstrated in Post #2.
Hi Adezii
I tried those parameters and it gave the same result
Regards Phill
Phill.
Would you like to attach sanitized copy of your db to the thread?
Hi Fishval
yes how do i do that
Regards Phill
- 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].
Thanks Fishval
Regards Phill
A don't have Access 2007.
Could you convert it to Access 2003 mdb file?
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.
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
-
Hi guys
Thats brilliant thank you so much
Its very much appreciated
Regards Phill
You are welcome, Phill.
BTW, the code could be further optimized if you like. So far it looks quite kludge.
Hi Fishval
that would be great if you have time
I always like to improve my knowledge any tips are much appreciated
Regards Phill
@FishVal
Hello FishVal, I checked my Dictionary of Computer Terms from top to bottom, but could not find any reference to quite kludge. (LOL).
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: BlackFireNova |
last post by:
Using Access 2002
I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan...
|
by: Brian Jorgenson |
last post by:
I am looking for a formula to put in my query to pull data based on
the last 3 months. It starts with the current day and will go back 3
months.
Here is my wrkflow language for example:
...
|
by: isetea |
last post by:
Hi,
I want to create a from where user can select from a date range / type in a date range to get only data from an underlying query within this range.
This should overwrite the existing criteria...
|
by: sixdeuce62 |
last post by:
Hello,
I am trying to create a query that will prompt me to enter the
parameter value if beginning date and ending date.
I have created everything I need in the query, but I have to manually
go...
|
by: flumpuk |
last post by:
Hi
My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created...
|
by: ali3n8 |
last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
|
by: zandiT |
last post by:
hello again
i have almost finished with my database. i have decided to generate the reports by using a date or date range and i can't get it to work. first i used parameters in a query but its...
|
by: tonymcc |
last post by:
Hi all
I am having a head bursting nightmare trying to get a query to work, I will try and explain.
I have developed a database for the calculating storage costs in a warehouse. Customers are...
|
by: phill86 |
last post by:
Hi
I have a form with two fields that represent a date range for example
20-7-09 to 26-7-09 I am running the query below and it only returns the date range from 20-7-09 to the 25-7-09 as you...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |