check if a date falls between a range

Where is this date you are checking? On a form? In another table?

"any new entries do not fall within those dates"
any new entries into what?

Assuming you have textboxes (txt_StartDate, txt_EndDate) on your form, and you want to determine whether any other employees overlap the dates on the form, try:

strCriteria = "[End_Date] >= #" & cdate(me.txt_StartDate) & "# AND " _
                   & "[Start_Date] <= #" & cdate(me.txt_EndDate) & "#"
if DCOUNT("ID", "yourTable", strCriteria) > 0 then
    msgbox "those dates are not allowed"
End If

If you want to actually return a recordset that identifies the employee and dates that conflict, the recordset would look something like:

SELECT Employee_ID, Start_Date, End_Date
FROM yourTable
WHERE [End_Date] >= Forms!yourForm.txt_StartDate
AND [Start_Date] <= Forms!yourForm.txt_EndDate

I have a form that has fields in it relating to the information in the table.

so a user manually enters a start date for an employees holiday, then the end date.. the system works out the number of days then the user clicks 'save'

Before I add the data to the database I need to check to see if there is already an entry for the dates the user has specified.  Because 2 employees cannot be on holiday at the same time.

so I need to check that the startdate and end date on the form I am trying to submit, do not lie in the range of dates already in the database.

Do you mean entry into another table, such as one assigning employees to tasks?  Please clarify.  Generally speaking, if you are working with a form bound to another table, you might (for example) have a subform on that bound bound to the Holidays table, showing the holiday date range for that employee.  Then you would need to check whether the date(s) for a task assignment fall into the holiday date range, and put up a warning message if so.

I also should have mentioned this is a c# 2010 winforms application... sorry

The syntax I provided give you the right results, just convert to C#.

The key is that you need to identify records where the [End_Date] already stored in the table is greater than the start date of the desired vacation, AND that the [Start_Date] stored in the table is less than the end date of the desired vacation.

This syntax will identify all four possible cases of overlap:
1.  The new dates span the entire period of some other vacation
2.  The new dates start before and end during the period of some other vacation
3.  The new dates start during and end after some other vacation
4.  The new dates start during and end during some other vacation

thanks gustav, that looks like the kind of logic I am after.

Cheers

Great. You are welcome!

/gustav