Problem with Date Query

[James]

I'm sure there is a simple answer to this but I just can't get my head
around it. I've got an annual leave database and am having trouble
querying for records.

In my leave table I have two fields [leave_start] and [leave_end]. Both
are date/time fields and are formatted dd/mm/yy. On a form
[Switchboard] I have two unbound controls [date1] and [date2] formatted
as dd/mm/yy.

I would like the users to enter dates in [date1] and [date2] to
retrieve annual leave records where people are on leave during that
period. I've got a command button that runs a query which includes
[date1] and [date2] but just cannot get the criteria in my query right.

I've tried the following in my query:
Where [leave_start]>=([Forms]![Switchboard]![Date1]) and
[leave_end]<=[Forms]![Switchboard]![Date2]
If for example [date1]=#21/08/06# and [date2]=#23/08/06# the above
wouldn't work for someone who is on leave 01/08/06-31/08/06 because the
[leave_start] is not after [date1] but that person would actually be on
leave during the specified period. I have also tried various other
permutations but with no luck.

I'm not entirely sure that [leave_start] needs to be involved in the
criteria at all but excluding it would give me all records going back
in time and I do need some kind of starting point based on the dates
entered on the form.

Any help would be greatly appreciated ;D)

Thanks
James

 

[Baz]

I'm sure there is a simple answer to this but I just can't get my head
around it. I've got an annual leave database and am having trouble
querying for records.

In my leave table I have two fields [leave_start] and [leave_end]. Both
are date/time fields and are formatted dd/mm/yy. On a form
[Switchboard] I have two unbound controls [date1] and [date2] formatted
as dd/mm/yy.

I would like the users to enter dates in [date1] and [date2] to
retrieve annual leave records where people are on leave during that
period. I've got a command button that runs a query which includes
[date1] and [date2] but just cannot get the criteria in my query right.

I've tried the following in my query:
Where [leave_start]>=([Forms]![Switchboard]![Date1]) and
[leave_end]<=[Forms]![Switchboard]![Date2]
If for example [date1]=#21/08/06# and [date2]=#23/08/06# the above
wouldn't work for someone who is on leave 01/08/06-31/08/06 because the
[leave_start] is not after [date1] but that person would actually be on
leave during the specified period. I have also tried various other
permutations but with no luck.

I'm not entirely sure that [leave_start] needs to be involved in the
criteria at all but excluding it would give me all records going back
in time and I do need some kind of starting point based on the dates
entered on the form.

Any help would be greatly appreciated ;D)

Thanks
James

[leave_start]<=[Forms]![Switchboard]![Date2] AND
[leave_end]>=[Forms]![Switchboard]![Date1]

In other words, the leave starts during or before the period in question,
and the leave ends during or after the period in question.

 

[Steve Schapel]

James,

Where [leave_start]<=[Forms]![Switchboard]![Date2] And
[leave_end]>=[Forms]![Switchboard]![Date1]

 

[James]

That's fantastic, thanks both!

Regards
James

James,

Where [leave_start]<=[Forms]![Switchboard]![Date2] And
[leave_end]>=[Forms]![Switchboard]![Date1]

--
Steve Schapel, Microsoft Access MVP

I'm sure there is a simple answer to this but I just can't get my head
around it. I've got an annual leave database and am having trouble
querying for records.

In my leave table I have two fields [leave_start] and [leave_end]. Both
are date/time fields and are formatted dd/mm/yy. On a form
[Switchboard] I have two unbound controls [date1] and [date2] formatted
as dd/mm/yy.

I would like the users to enter dates in [date1] and [date2] to
retrieve annual leave records where people are on leave during that
period. I've got a command button that runs a query which includes
[date1] and [date2] but just cannot get the criteria in my query right.

I've tried the following in my query:
Where [leave_start]>=([Forms]![Switchboard]![Date1]) and
[leave_end]<=[Forms]![Switchboard]![Date2]
If for example [date1]=#21/08/06# and [date2]=#23/08/06# the above
wouldn't work for someone who is on leave 01/08/06-31/08/06 because the
[leave_start] is not after [date1] but that person would actually be on
leave during the specified period. I have also tried various other
permutations but with no luck.

I'm not entirely sure that [leave_start] needs to be involved in the
criteria at all but excluding it would give me all records going back
in time and I do need some kind of starting point based on the dates
entered on the form.

Any help would be greatly appreciated ;D)

Thanks
James

 

[Rod Plastow]

Hi,

I have for a long time avoided date formats preferring long integers in the
format yyyymmdd. However in my most recent project I decided to use date
format fields. All afternoon I have been tearing my hair out (and there
wasn't much to start with) because my queries were giving the wrong results.
Finally I stumbled upon this from MS Help system.

Use International Date Formats in SQL Statements
See AlsoSpecificsYou must use English (United States) date formats in SQL
statements in Visual Basic. However, you can use international date formats
in the query design grid.

OK, I'm an 'Alien,' that is I'm not American and use UK date formats. No
wonder my SQL was not working, even using a "#" & <mydate> & "#" construct
did not give the correct results.

James: I offer this post in case you encounter the same scenario.

Regards,

Rod

That's fantastic, thanks both!

Regards
James

James,

Where [leave_start]<=[Forms]![Switchboard]![Date2] And
[leave_end]>=[Forms]![Switchboard]![Date1]

--
Steve Schapel, Microsoft Access MVP

I'm sure there is a simple answer to this but I just can't get my head
around it. I've got an annual leave database and am having trouble
querying for records.

In my leave table I have two fields [leave_start] and [leave_end]. Both
are date/time fields and are formatted dd/mm/yy. On a form
[Switchboard] I have two unbound controls [date1] and [date2] formatted
as dd/mm/yy.

I would like the users to enter dates in [date1] and [date2] to
retrieve annual leave records where people are on leave during that
period. I've got a command button that runs a query which includes
[date1] and [date2] but just cannot get the criteria in my query right.

I've tried the following in my query:
Where [leave_start]>=([Forms]![Switchboard]![Date1]) and
[leave_end]<=[Forms]![Switchboard]![Date2]
If for example [date1]=#21/08/06# and [date2]=#23/08/06# the above
wouldn't work for someone who is on leave 01/08/06-31/08/06 because the
[leave_start] is not after [date1] but that person would actually be on
leave during the specified period. I have also tried various other
permutations but with no luck.

I'm not entirely sure that [leave_start] needs to be involved in the
criteria at all but excluding it would give me all records going back
in time and I do need some kind of starting point based on the dates
entered on the form.

Any help would be greatly appreciated ;D)

Thanks
James

 

[Steve Schapel]

Rod,

Good to bring this up. James would not have any problem with this in
the example we were discussing, as he was using the value of form
controls in the criteria of a query. No problem.

There is a good article on this topic at
http://www.allenbrowne.com/ser-36.html

On the other hand, I personally always find it easier to use the
numerical value of a date, when working with it in code. So, in the
case of James's example, my SQL within a VBA procedure would look like...
" WHERE [leave_start]<=" & CLng(Me.Date2) & " AND [leave_end]>=" &
CLng(Me.Date1)

Mind you, this is quite different from using an integer like yyyymmdd.
I applaud your decision to move to using Date/Time data type fields, as
Access provides such rich date-related functionality.

 

[Rod Plastow]

Hi Steve,

I bow to your applause No seriously thanks for that; it never occurred
to me to use a CLng(date) construct; I was using a function to re-engineer
the date as an American format string - messy!

I'll check out Allen's article.

Regards,

Rod

Rod,

Good to bring this up. James would not have any problem with this in
the example we were discussing, as he was using the value of form
controls in the criteria of a query. No problem.

There is a good article on this topic at
http://www.allenbrowne.com/ser-36.html

On the other hand, I personally always find it easier to use the
numerical value of a date, when working with it in code. So, in the
case of James's example, my SQL within a VBA procedure would look like...
" WHERE [leave_start]<=" & CLng(Me.Date2) & " AND [leave_end]>=" &
CLng(Me.Date1)

Mind you, this is quite different from using an integer like yyyymmdd.
I applaud your decision to move to using Date/Time data type fields, as
Access provides such rich date-related functionality.

--
Steve Schapel, Microsoft Access MVP

Hi,

I have for a long time avoided date formats preferring long integers in the
format yyyymmdd. However in my most recent project I decided to use date
format fields. All afternoon I have been tearing my hair out (and there
wasn't much to start with) because my queries were giving the wrong results.
Finally I stumbled upon this from MS Help system.

Use International Date Formats in SQL Statements
See AlsoSpecificsYou must use English (United States) date formats in SQL
statements in Visual Basic. However, you can use international date formats
in the query design grid.

OK, I'm an 'Alien,' that is I'm not American and use UK date formats. No
wonder my SQL was not working, even using a "#" & <mydate> & "#" construct
did not give the correct results.

James: I offer this post in case you encounter the same scenario.