 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Between Date
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 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, > [quoted text clipped - 13 lines] > Thanks > Chi Thank you soooo much!!! It is perfect! > WHERE StartDate >= [From this date] AND EndDate <= [To this date] > [quoted text clipped - 25 lines] > > Thanks > > 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] > [quoted text clipped - 25 lines] > > Thanks > > Chi 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, > [quoted text clipped - 46 lines] >> > Thanks >> > 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] > [quoted text clipped - 25 lines] > > Thanks > > 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, > [quoted text clipped - 50 lines] >> > Thanks >> > 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 > > Student2 02/16/07 05/01/08 > > Student3 04/01/06 04/01/07 > > Student4 01/01/06 01/14/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: > [quoted text clipped - 59 lines] > >> > Thanks > >> > 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: > [quoted text clipped - 59 lines] > >> > Thanks > >> > Chi 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, > [quoted text clipped - 83 lines] >> >> > Thanks >> >> > Chi Hi Michel, Thanks for all your help. I got my last question. > It seems you wish to detect even partial overlap: > [quoted text clipped - 59 lines] > >> > Thanks > >> > Chi |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.