Between Date

[Chi]

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]â€criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Michel Walsh]

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates you
supply as parameter.



Vanderghast, Access MVP

 

[Chi]

Thank you soooo much!!!
It is perfect!

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates you
supply as parameter.



Vanderghast, Access MVP

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Chi]

Hi Michel,

I used the “ WHERE StartDate >= [From this date] AND EndDate <= [To this
date]". Thanks. It works perfectly!!!!
--------------------------
I have another question, please.

I would like to have these dates appear on my report. Normally, I use the
text box and add the criteria in the textbox's record source. However, I got
an error message for this time.

Would you please help me on what I need to put in the textbox record source?

Thanks

Chi

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates you
supply as parameter.



Vanderghast, Access MVP

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Michel Walsh]

If you add the parameter in the SELECT list, that should make it available
to your report based on such a query.


Vanderghast, Access MVP

Hi Michel,

I used the " WHERE StartDate >= [From this date] AND EndDate <= [To
this
date]". Thanks. It works perfectly!!!!
--------------------------
I have another question, please.

I would like to have these dates appear on my report. Normally, I use the
text box and add the criteria in the textbox's record source. However, I
got
an error message for this time.

Would you please help me on what I need to put in the textbox record
source?

Thanks

Chi

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Chi]

Hi Michel,

To continue on what you helped me last time, I have more questions about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have
student1 , student 2 and student 3 in my query since either their StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07), but not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates you
supply as parameter.



Vanderghast, Access MVP

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Michel Walsh]

It seems you wish to detect even partial overlap:



SELECT *
FROM somewhere
WHERE [parameterStartDate] <= EndDate AND [parameterEndDate] >= StartDate



Hoping it may help
Vanderghast, Access MVP

Hi Michel,

To continue on what you helped me last time, I have more questions about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have
student1 , student 2 and student 3 in my query since either their
StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07), but
not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP

Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Chi]

Good Morning Michel,

I very appreciate for all your help! I will try to it now. And let you know
what I get.

------------------
Actually, I would like to know how many students currently in the class.
Since they are come and go at different times, it is hard to fix it out who
are still in class at period of time or now.

startdate EndDate
Student 1 01/15/07 12/15/07
Ex: I would like to know who are still in class now (11/29/07) or at
between 01/15/07 and 12/15/07?


Again I will apply your last response first and let you know.

Thank you so much!
Chi

It seems you wish to detect even partial overlap:



SELECT *
FROM somewhere
WHERE [parameterStartDate] <= EndDate AND [parameterEndDate] >= StartDate



Hoping it may help
Vanderghast, Access MVP

Hi Michel,

To continue on what you helped me last time, I have more questions about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have
student1 , student 2 and student 3 in my query since either their
StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07), but
not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP


Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Chi]

Hi Michel,

Perfect!!!!! It works well after inserting your code to the SQL place!

---------------
I have another question, please. Since I created a combo box to choose the
Department names, the WHERE section is more complicates. Therefore, when I
ran the query, I got the error message " too complex or the expression is
typed incorrectly." Please see the code below. Thank you so much!

WHERE (((Conjuntion.Department)=Forms!frmDepartmentDateRange!Combo0)) &
[Between this date]<=EndDate And [And this date]>=StartDate;
--------------------------------------------

I added the "&" on it. Do you think that causes the problem?
Again, your code works perfectly without the combo box.
--------------------------------------------------------------------------------

It seems you wish to detect even partial overlap:



SELECT *
FROM somewhere
WHERE [parameterStartDate] <= EndDate AND [parameterEndDate] >= StartDate



Hoping it may help
Vanderghast, Access MVP

Hi Michel,

To continue on what you helped me last time, I have more questions about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have
student1 , student 2 and student 3 in my query since either their
StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07), but
not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP


Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Michel Walsh]

should use AND, not &



WHERE (((Conjuntion.Department)=Forms!frmDepartmentDateRange!Combo0)) AND
[Between this date]<=EndDate And [And this date]>=StartDate;




Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Perfect!!!!! It works well after inserting your code to the SQL place!

---------------
I have another question, please. Since I created a combo box to choose the
Department names, the WHERE section is more complicates. Therefore, when I
ran the query, I got the error message " too complex or the expression is
typed incorrectly." Please see the code below. Thank you so much!

WHERE (((Conjuntion.Department)=Forms!frmDepartmentDateRange!Combo0)) &
[Between this date]<=EndDate And [And this date]>=StartDate;
--------------------------------------------

I added the "&" on it. Do you think that causes the problem?
Again, your code works perfectly without the combo box.
--------------------------------------------------------------------------------

It seems you wish to detect even partial overlap:



SELECT *
FROM somewhere
WHERE [parameterStartDate] <= EndDate AND [parameterEndDate] >=
StartDate



Hoping it may help
Vanderghast, Access MVP

Hi Michel,

To continue on what you helped me last time, I have more questions
about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to
have
student1 , student 2 and student 3 in my query since either their
StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07),
but
not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:


WHERE StartDate >= [From this date] AND EndDate <= [To this
date]


will pick records where the event described by the record occurs
entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP


Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi

 

[Chi]

Hi Michel,

Thanks for all your help. I got my last question.

It seems you wish to detect even partial overlap:



SELECT *
FROM somewhere
WHERE [parameterStartDate] <= EndDate AND [parameterEndDate] >= StartDate



Hoping it may help
Vanderghast, Access MVP

Hi Michel,

To continue on what you helped me last time, I have more questions about
this, please.

StartDate EndDate
Student 1 01/15/07 12/15/07
Student2 02/16/07 05/01/08
Student3 04/01/06 04/01/07
Student4 01/01/06 01/14/07

If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have
student1 , student 2 and student 3 in my query since either their
StartDate
or EndDate fall between these dates ( between 01/15/07 and 12/15/07), but
not
student 4 since his StartDate and EndDate is not between 01/15/07 and
12/15/07.

Would you please help me on this new criteria?

Thanks
Chi






:

WHERE StartDate >= [From this date] AND EndDate <= [To this date]


will pick records where the event described by the record occurs entirely
between the two dates you supply as parameter.



WHERE EndDate >= [From this date] AND StartDate <= [To this date]


will pick records where the event occurs in part between the two dates
you
supply as parameter.



Vanderghast, Access MVP


Hi,

I have two fields: StartDate and EndDate.
Ex: StartDate EndDate
01/15/07 05/12/07
02/16/07 08/10/08
03/25/07 12/12/08
02/12/07 12/08/08

In my query screen, I use "Between [From this date] And [To this
date]"criteria under StartDate field. It works fine.

For this time, I would like to pull out the records from two fields:
StartDate and EndDate. Would you please help me on the criteria?

Thanks
Chi