How do you count mutiple occurences of a range of dates in a table?

[wtussey]

I'm trying to put together a soultion for a user who wants to query a table containing dates of people on leave from an office. The table has a "start leave" date, and an "end leave" date for each person planning to be on leave away from the office. What the customer wants is a query that looks at the start dates and end dates, and comes up with a numerical count of who'll be on leave on a given day.<br>
<br>
My guess was to use Between() and to count the results, but I'm trying to set this up as a validation rule for data entry. HOW IT WILL WORK: When a user attempts to enter new leave dates in a form, if the dates cross over into a period when many other people are attempting to take leave, the application will kick back a warning saying a "maximum number" of people are attempting to take leave for a given period of dates.<br>
<br>
Any insights would be greatly appreicated!<br>
<br>
-Bill<br>

 

[calahans]

Why don't you try to use the lost_focus event on the text box to run the following SQL:<br>
<br>
SELECT COUNT(*) FROM TABLE<br>
WHERE DATE BETWEEN 'TEXT1.TEXT' AND 'TEXT2.TEXT'<br>
<br>
Obviously the syntax above is incorrect. You would need to strings etc to get the correct values something like this:<br>
<br>
dim str as string<br>
<br>
str = "SELECT COUNT(*) FROM TABLE<br>
WHERE DATE BETWEEN "<br>
str = str & text1.text .....<br>
<br>
You get the idea.<br>
<br>
C<br>