Query date range <=

[phill86]

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

SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Duration, TblCalendarS104.Location
FROM TblCalendarS104
WHERE (((TblCalendarS104.StartDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((TblCalendarS104.EndDate)<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]))
ORDER BY TblCalendarS104.StartDate;

[ChipR]

Take a look at this recent thread: Filter Form By Date Criteria

[phill86]

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

[ChipR]

Have some instructions to make sure your user enters the date in the correct format. Then, make sure you use the #'s.

[phill86]

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

[missinglinq]

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)>

[FishVal]

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.

[phill86]

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

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

but now the recordset set that I am using on the query will not run properly it gets to the

Do While Not rstCheck.EOF

line and skips to the

rstCheck.Close

line where as with the previous query it worked

here is my recordset code

 
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
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date
 
'this checks to see if euipment is booked then populates sform with time info which is then picked up by conditional formatting
 
 'works out the first and last day of the week acoording to the selected date from mydate
 
 
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].[SfrCalS104]![TxtMon] = FirstDay
[Forms]![frmcalendarmain].[SfrCalS104]![TxtSun] = LastDay
 
MondayForm = [Forms]![frmcalendarmain].[SfrCalS104]![TxtMon]
 
Set dbsEquipBook = CurrentDb()
 Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters(0) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtMon]
qdf.Parameters(1) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtSun]
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
 
Do While Not rstCheck.EOF
 
    BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
    BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
    BookedDay = Format(rstCheck!StartDate, "ddd")
 
    If BookedEndTime = "00:00:00" Then
 
        BookedEndTime = "23:59:59"
 
End If
 
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
 
 

Apologies if this is getting anouying but I really need to get this working

Thanks for any help

hopefully Phill

[FishVal]

Phil, did you understand my explanation?

Kind regards,
Fish.

[phill86]

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

[FishVal]

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.

[phill86]

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

 
WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
 

Any help is much appreciated

Phill

[Stewart Ross]

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:

WHERE (((DateValue([TblCalendarS104].[StartDate]))>=# " & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] & "#) AND ((DateValue([TblCalendarS104].[EndDate]))<= #" & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun] & " # ));"
 

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

[phill86]

Hi Stewart,

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

Your help is very much appreciated

Regards Phill

[phill86]

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

SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Duration, TblCalendarS104.Location
FROM TblCalendarS104
WHERE "(((DateValue([TblCalendarS104].[StartDate]))>=# " & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] & "#) AND ((DateValue([TblCalendarS104].[EndDate]))<= #" & [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun] & " # ));" 
 

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

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
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date
 
'works out the first and last day of the week acoording to the selected date from mydate
 
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
 
MondayForm = [Forms]![frmcalendarmain].[SfrCalS104]![TxtMon]
 
Set dbsEquipBook = CurrentDb()
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters(0) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtMon]
qdf.Parameters(1) = [Forms]![frmcalendarmain]![SfrCalS104].[Form]![TxtSun]
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset)
 
Do While Not rstCheck.EOF
 
    BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
    BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
    BookedDay = Format(rstCheck!StartDate, "ddd")
 
    If BookedEndTime = "00:00:00" Then
 
        BookedEndTime = "23:59:59"
 
End If
 
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
 

SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Location, TblCalendarS104.Duration, DateValue([TblCalendarS104].[StartDate]) AS Expr1
FROM TblCalendarS104
GROUP BY TblCalendarS104.StartDate, TblCalendarS104.EndDate, TblCalendarS104.Location, TblCalendarS104.Duration
HAVING (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon] And (DateValue([TblCalendarS104].[StartDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
 

[ChipR]

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.

[phill86]

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

[ChipR]

According to documentation on the DateValue function, that would depend on the Short Date format set for your system.

[NeoPa]

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.

[FishVal]

@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.

[NeoPa]

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.

[FishVal]

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

[NeoPa]

I would agree. I missed that detail.

This would be appropriate for a string, but less so for a date value.

[phill86]

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

SELECT TblCalendarS104.StartDate, TblCalendarS104.EndDate
FROM TblCalendarS104
WHERE (((TblCalendarS104.StartDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtMon]) AND ((TblCalendarS104.EndDate)>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]));
 

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

 
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
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date
 
'works out the first and last day of the week acoording to the selected date from mydate
 
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
 
MondayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon]
 
Set dbsEquipBook = CurrentDb()
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtMon]
''qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtSun]
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset)
 
'' Set rstCheck = _
  ''    dbsEquipBook.OpenRecordset("QSelS104calcheck", dbOpenDynaset)
 
 
Do While Not rstCheck.EOF
 
    BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
    BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
    BookedDay = Format(rstCheck!StartDate, "ddd")
 
    If BookedEndTime = "00:00:00" Then
 
        BookedEndTime = "23:59:59"
 
End If
 
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 very much appreciated

Regards Phill