hi,
I have an unbound mainform where i am getting 1 start date and 1 end date and a bound subform where multiple records for a person with start date and end date for specific assignments.  

i want to check whether there is any record where my new start date is between any assignments

eg: assign1   10/02/2011 to 15/03/2011
   assign2   16/03/2011 to 25-03-2011
   etc

new assignment start date in main form 10/03/2011  to 08/04/2011
before updating to this table thru a sqlstatement, i want to check whether this person have any assignments in this period fully or partly

---

Richard Responds:

You could do this with a series of DLOOKUP statements. Let's say you have a table that has all of a user's current appointments:

AppointmentT: AppointmentID, UserID, StartTime, EndTime

Now you want to check to see if he has an appointment scheduled at a specific date/time (let's call it "T"). You could say:

ID = DLOOKUP("AppointmentID","AppointmentT","StartTime<=#" & T & "# AND EndTime>=#" & T & "#")

So if he has an appointment scheduled for March 1st from 9am to 11am, and you try looking for 3/1 at 10am, it will return an ID of that scheduled appointment. If not, you'll get a NULL value, meaning he's free.

Of course, there's a lot more to it than this... we're not checking for time overlaps or anything complicated, but this should get you started.

Here's a free tutorial on DLOOKUP to get you started:

http://599cd.com/tips/access/dlookup-function/?key=AlExBlog