By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Query date range <=

100+
P: 121
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 can see I am using the less or equal to criteria on the 2nd field so this should in theory give me 20-7-09 to 26-7-09 Is this criteria correct or am I just being Dumb

Regards Phill
Expand|Select|Wrap|Line Numbers
  1. SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Duration, TblCalendarS104.Location
  2. FROM TblCalendarS104
  3. WHERE (((TblCalendarS104.StartDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((TblCalendarS104.EndDate)<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]))
  4. ORDER BY TblCalendarS104.StartDate;
Jul 27 '09 #1
Share this Question
Share on Google+
23 Replies


Expert 100+
P: 1,287
Take a look at this recent thread: Filter Form By Date Criteria
Jul 27 '09 #2

100+
P: 121
OK I think I have worked out why it is not working its because the table that contains the data which is sourced from an outlook calendar stores the date as follows

02/08/2009 13:00:00

This is the format that is copied from outlook and I would much prefer to keep it in this format because I have used it in my code throughout and it would be a major pain formatting it can you see any other way around it?

Cheers Phill
Jul 28 '09 #3

Expert 100+
P: 1,287
Have some instructions to make sure your user enters the date in the correct format. Then, make sure you use the #'s.
Jul 28 '09 #4

100+
P: 121
Hi ChipR,

Thanks for the reply

The user does not input the date. A date range is selected from a calendar and then a text box is populated on the form the query criteria takes the date range from the form

not sure what you mean by the #'s or where i should put it
Jul 29 '09 #5

missinglinq
Expert 2.5K+
P: 3,532
If you look at Post # 2 in the linked thread Chip gave you above you'll see what he means/where it goes. Pound signs (#)are used by Access in expressions as delimiters to show that a field/control name is a Date/Time datatype, in the same manner that quotation marks in expressions indicate that a field/control name represents a Text value.

Linq ;0)>
Jul 29 '09 #6

FishVal
Expert 2.5K+
P: 2,653
Phil,

Since you most likely have dates in your table without time portion aka with default 00:00, almost every date will be less than those imported from outlook and having non-zero time portion.

Regards,
Fish.

P.S. There is a bit less than nothing to do here with format and/or delimiters.
Jul 29 '09 #7

100+
P: 121
OK I'm still not getting this I have tried the "#" everywhere I can think of and it does not seem to be working apologies if I am being dumb but this is getting really frustrating and any help is much appreciated

I have however got the query working how I want it to by using datevalue in the following SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1, DateValue([TblCalendarS104].[EndDate]) AS Expr2
  2. FROM TblCalendarS104
  3. WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
  4.  
but now the recordset set that I am using on the query will not run properly it gets to the
Expand|Select|Wrap|Line Numbers
  1. Do While Not rstCheck.EOF
line and skips to the
Expand|Select|Wrap|Line Numbers
  1. rstCheck.Close
line where as with the previous query it worked

here is my recordset code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strControlName As String
  3. Dim dbsEquipBook As DAO.Database
  4. Dim rstCheck As DAO.Recordset
  5. Dim qdf As DAO.QueryDef
  6. Dim BookedStartTime As Date
  7. Dim BookedEndTime As Date
  8. Dim MyDate, MyWeekDay
  9. Dim FirstDay As Date
  10. Dim LastDay As Date
  11. Dim MondayForm As Date
  12.  
  13. 'this checks to see if euipment is booked then populates sform with time info which is then picked up by conditional formatting
  14.  
  15.  'works out the first and last day of the week acoording to the selected date from mydate
  16.  
  17.  
  18. MyDate = [Forms]![frmcalendarmain].[frmCalendar]![txtDate]
  19. MyWeekDay = Weekday(MyDate)
  20.  
  21.  
  22. FirstDay = MyDate - MyWeekDay + 2
  23. LastDay = MyDate - MyWeekDay + 8
  24.  
  25. 'writes the first and last date to form
  26.  
  27.  
  28. [Forms]![frmcalendarmain].[SfrCalS104]![TxtMon] = FirstDay
  29. [Forms]![frmcalendarmain].[SfrCalS104]![TxtSun] = LastDay
  30.  
  31. MondayForm = [Forms]![frmcalendarmain].[SfrCalS104]![TxtMon]
  32.  
  33. Set dbsEquipBook = CurrentDb()
  34.  Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
  35.  
  36. qdf.Parameters(0) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtMon]
  37. qdf.Parameters(1) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtSun]
  38.  
  39. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  40.  
  41. Do While Not rstCheck.EOF
  42.  
  43.     BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
  44.     BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
  45.     BookedDay = Format(rstCheck!StartDate, "ddd")
  46.  
  47.     If BookedEndTime = "00:00:00" Then
  48.  
  49.         BookedEndTime = "23:59:59"
  50.  
  51. End If
  52.  
  53. For i = 8 To 23
  54.   For j = 0 To 1
  55.     If i < 10 Then
  56.       strControlName = "txt" & BookedDay & "0" & i & ":"
  57.     Else
  58.       strControlName = "txt" & BookedDay & i & ":"
  59.     End If
  60.     If j = 0 Then
  61.       strControlName = strControlName & "00"
  62.     Else
  63.       strControlName = strControlName & "30"
  64.     End If
  65.    If Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then
  66.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191)
  67.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191)
  68.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).Enabled = False
  69.     End If
  70.  
  71.   Next
  72. Next
  73.  
  74.  
  75.  rstCheck.MoveNext
  76. Loop
  77.  
  78. rstCheck.Close
  79.  
  80. End Sub
  81.  
  82.  
Apologies if this is getting anouying but I really need to get this working

Thanks for any help

hopefully Phill
Jul 31 '09 #8

FishVal
Expert 2.5K+
P: 2,653
Phil, did you understand my explanation?

Kind regards,
Fish.
Jul 31 '09 #9

100+
P: 121
Hi Fish,

No sorry I didnt

The frustrating thing is that I can get the query working now but for some reason the recordset is not recognising the fact that there are records in the set

Any ideas are much appreciated

Cheers Phill
Jul 31 '09 #10

FishVal
Expert 2.5K+
P: 2,653
Sorry, Phil.

I didn't read your post carefully, otherwise I could see you've done using DateValue() function exactly what I was pointing for.

As for your current problem.
What for do you pass parameters to the query? It doesn't seem to have any.
Jul 31 '09 #11

100+
P: 121
Hi Fish Val

Thanks for the reply I have tried the recordset without passing the parameters to the query and i get the error run-time error 3061

Too few parameters expected 2

I believe the parameters are in the following SQL which is pointing to a control on my form which holds the date value
Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
  3.  
Any help is much appreciated

Phill
Aug 3 '09 #12

Expert Mod 2.5K+
P: 2,545
Hi Phill. Apologies for not being around much lately - other commitments have intervened.

Anyway, Access querydefs cannot interpret form controls in the way you are passing them - it leads to the 'too few parameters' message. It is possible to pass the parameters to the query (by setting the values as querydef parameters), but the simplest way is to pass the value of the form control in the SQL statement, not the string literal referring to the control by name. This can be done as follows:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((DateValue([TblCalendarS104].[StartDate]))>=# " & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] & "#) AND ((DateValue([TblCalendarS104].[EndDate]))<= #" & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun] & " # ));"
  2.  
You need to make sure the string open and close quotes go in the right places - the code extract above does not represent the whole SQL string so I have not placed an opening double quote before the WHERE part.

When date literals are included in SQL strings it is easy to get comparisons wrong. Standard (ANSI) SQL expects date literals to be month-first (m/d/y) format, regardless of any regional variations. This can lead to erratic results when using forms with dates included as text values which then have to be converted to proper dates (numeric values) by Access itself or in code, as you are doing.

-Stewart
Aug 11 '09 #13

100+
P: 121
Hi Stewart,

Thanks for that I will give it a try and let you know.

Your help is very much appreciated

Regards Phill
Aug 15 '09 #14

100+
P: 121
Hi Stewart,

Sorry I have not been in touch lately I have been away for a while.

I could not get the solution you gave me to work the query ran, after I had put the (") in, but it brings up all the records in the table and not those defined by the date range

Expand|Select|Wrap|Line Numbers
  1. SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Duration, TblCalendarS104.Location
  2. FROM TblCalendarS104
  3. WHERE "(((DateValue([TblCalendarS104].[StartDate]))>=# " & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] & "#) AND ((DateValue([TblCalendarS104].[EndDate]))<= #" & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun] & " # ));" 
  4.  
I then had a look through some old threads and found the solution to a similair problem I was having a while back the following SQL works and brings the date range that is specified which is what I want it to do....Great!!

Until you try and run the recordset which contains the query on dates that are in the first week of the month

So for example if the date range is

7/9/09 to the 13/9/09 it works but
1/9/09 to the 6/9/09 does not work

and even more bizarrely

1/6/09 to 7/6/09 and the 1/12/08 to 7/12/08 works and the only difference I can see is that the 1st is at the start of the week

The reordset seems to think that it is at EOF when it does not work but when I run the query it is working and contains the records that have been specified in the date range

please see the code for the reordset and and SQL for the query below.

I hope my ramblings make sense and any help is much appreciated
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
  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 acoording to the selected date from mydate
  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. MondayForm = [Forms]![frmcalendarmain].[SfrCalS104]![TxtMon]
  26.  
  27. Set dbsEquipBook = CurrentDb()
  28.     Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
  29.  
  30. qdf.Parameters(0) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtMon]
  31. qdf.Parameters(1) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtSun]
  32.  
  33. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset)
  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.     If BookedEndTime = "00:00:00" Then
  42.  
  43.         BookedEndTime = "23:59:59"
  44.  
  45. End If
  46.  
  47. For i = 8 To 23
  48.   For j = 0 To 1
  49.     If i < 10 Then
  50.       strControlName = "txt" & BookedDay & "0" & i & ":"
  51.     Else
  52.       strControlName = "txt" & BookedDay & i & ":"
  53.     End If
  54.     If j = 0 Then
  55.       strControlName = strControlName & "00"
  56.     Else
  57.       strControlName = strControlName & "30"
  58.     End If
  59.    If Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then
  60.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191)
  61.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191)
  62.       Forms![frmcalendarmain].[SfrCalS104].Form.Controls(strControlName).Enabled = False
  63.     End If
  64.  
  65.   Next
  66. Next
  67.  
  68.  
  69.  rstCheck.MoveNext
  70. Loop
  71.  
  72. rstCheck.Close
  73.  
Expand|Select|Wrap|Line Numbers
  1. SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Location, TblCalendarS104.Duration, DateValue([TblCalendarS104].[StartDate]) AS Expr1
  2. FROM TblCalendarS104
  3. GROUP BY TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Location, TblCalendarS104.Duration
  4. HAVING (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] And (DateValue([TblCalendarS104].[StartDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
  5.  
Sep 8 '09 #15

Expert 100+
P: 1,287
I suspect this is because dates like 1/1/2001 are ambiguous. Access by default assumes that you are giving it Month/Day/Year. Only when you give it a date which can not be in that format will it realize that you are using Day/Month/Year. The easiest solution I know of is to give Access what it wants.
Sep 8 '09 #16

100+
P: 121
Hi Chipr,

Thats what I thought I was handling by using the datevale function in the SQL the strange thing is that the underlying query works and there are records in the recordset but it does not recognise the dates at the begining of the month any help is much appreciated

Regards Phill
Sep 8 '09 #17

Expert 100+
P: 1,287
According to documentation on the DateValue function, that would depend on the Short Date format set for your system.
Sep 8 '09 #18

NeoPa
Expert Mod 15k+
P: 31,709
Phill,

Looking at your OP, it seems that you are looking specifically for items that fall wholly and completely within the date range specified.

As mentioned earlier, as the Outlook dates are generally Date/Time values - and therefore numerically greater than the date from the form, which matches the date part only, it will quite correctly exclude those items where the dates match the Sunday date.

Does that help to clarify matters?

If you can specify exactly what you're intending to do (an important first step before even attempting to code) then I think we may be able to help you achieve your required results.
Sep 8 '09 #19

FishVal
Expert 2.5K+
P: 2,653
@ChipR
Just a thought.

It may be beneficial to get rid of use this unreliable function and use more consistent logic which doesn't suffer from any format agreements.

Like

DateWithoutTime = DateSerial(Year(DateWithTime), Month(DateWithTime), Day(DateWithTime))

or even

DateWithoutTime = Int(DateWithTime)

Regards,
Fish.
Sep 10 '09 #20

NeoPa
Expert Mod 15k+
P: 31,709
I'm not sure I agree with that Fish.

If the data that is being passed to the function is not in an ambiguous format, then there is no problem. Even if it were to be, then interpreting it locally would make more sense than hard-coding an interpretation oneself.

Also, although we know how the date/time is stored internally (at the moment at least) I'm not sure we should advise reliance on this feature.

Please excuse this critique of your comments. I speak as an admirer of most of what you post.
Sep 10 '09 #21

FishVal
Expert 2.5K+
P: 2,653
Hello, NeoPa.

You don't need to apologize since I appreciate your point of view as valuable contribution to the discussion and don't take it as personal criticism (there is really no reason to take it as such).

To justify my point of view I would say that sometimes problem could be solved by just dropping current method and using another one when all rational ideas have failed.
IMHO, this DateValue() function is unreasonably messed having argument declared as String which is supposed to be interpreted according to computer regional settings. It is good for working with text input (which it is suited for I suppose) but looks very suspicious when working with Date variable which, I guess, is being implicitly converted to String before passing as argument.

Regards,
Fish
Sep 10 '09 #22

NeoPa
Expert Mod 15k+
P: 31,709
I would agree. I missed that detail.

This would be appropriate for a string, but less so for a date value.
Sep 10 '09 #23

100+
P: 121
Hi,

Thanks for all of your responses however I am getting very confused and I still do not have an answer to this problem.

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 beleive 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 am 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 as follows

Expand|Select|Wrap|Line Numbers
  1. SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate
  2. FROM TblCalendarS104
  3. WHERE (((TblCalendarS104.StartDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtMon]) AND ((TblCalendarS104.EndDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
  4.  
I then want to use this query within my recordset which is designed to set the controls on my form to different colours to represent the calendar bookings as follows

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

Regards Phill
Sep 11 '09 #24

Post your reply

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