Hi ,

I am developing an application in c#.net with Ms-Access as the database. I need to findout whether a date entered by the user falls between the 'from_date' & 'to_date' fields in a table.

Ex:

sno from date To date
1 08-jul-09 09-jul-09
2 14-jul-09 16-jul-09
3 13-jul-09 13-jul-09
4 21-jul-09 22-jul-09
5 06-jul-09 06-jul-09
6 01-jul-09 01-jul-09


I have used the query

string sdatecheck = "select * from LeaveMaster_Table where ( #" + this.Txt_Fromdate.Text + "# >= From_Date and #" + this.Txt_Fromdate.Text + "# <= To_Date) or (#" + this.Txt_Fromdate.Text + "# = From_Date and #" + this.Txt_Fromdate.Text + "#= To_Date)and Emp_ID = '" + ssempid.ToString() + "' and If_Approved = 'APPROVED'";

if I enter from_date as 7/2/2009 & to_date as 07/13/2009 it did not brings out the records 1, 3 & 5 as per the table.

Any help would be appreciated....

Thanks,
Karthi

Hi - You have allowed for the FromDate being the start date and also in the range between the start and end dates.

You dont appear to have allowed for the other scenario where the FromDate may be outside of the start of the range, and so the ToDate needs to be considered to determine if the tail end of the date range fits the range defined in the table...so :

string sdatecheck = "select * from LeaveMaster_Table where ( #" + this.Txt_Fromdate.Text + "# >= From_Date and #" + this.Txt_Fromdate.Text + "# <= To_Date) or (#" + this.Txt_Fromdate.Text + "# = From_Date and #" + this.Txt_Fromdate.Text + "#= To_Date) or (#" + this.Txt_Todate.Text + "# >= From_Date and #" + this.Txt_Todate.Text + "#<= To_Date) and Emp_ID = '" + ssempid.ToString() + "' and If_Approved = 'APPROVED'";


Let us know how you get on...!

and Emp_ID = '" + ssempid.ToString() + "' and If_Approved = 'APPROVED'";

Hi GUIDO22,

Thank you very much for your reply. I have solved that issue by using the following query,

string sdatecheck = "select * from leavemaster_table where (from_date <= #" + this.Txt_Fromdate.Text + "# and to_date >= #" + this.Txt_Fromdate.Text + "#) or (from_date <= #" + this.Txt_Todate.Text + "# and to_date >= #" + this.Txt_Todate.Text + "#) or (#" + this.Txt_Todate.Text + "# = From_Date and #" + this.Txt_Todate.Text + "#= To_Date) and emp_id = '104' and if_approved = 'APPROVED'";

Which is similar to your reply.:)

Thanks again !!!