Solved To Check if the data is alreday there? (1 Viewer)

[sbaud2003]

Hello Sir/Madam
Is there any method to check if the same value is already taken in the Data base. I mean suppose a person have taken leave from 12-09-23 to 25-09-23.
(two Date fields). If some body tries to enter the value for leave from 13-09-23 to 14-09-23 for the same person, it should flash error mesage. is it possible.

 

[theDBguy]

That should be possible but may require some code to get it done.

 

[The_Doc_Man]

In general, there are ways to determine if a given date range is in a table. How you set this up would govern what the solution would look like. However, the concept is basically, you have to have two DATE fields in your table and a DATE field that you want for comparison. Then look for cases where the comparison date is between the starting and ending date from the table.

IF you do this from a form where the comparison date is called CompDate and the fields in the table are LeaveStart and LeaveEnd then you could build a query that resembles the following:

SELECT COUNT(*) AS Conflicts FROM table-holding-leave WHERE [Forms]![this-form-name]![CompDate] BETWEEN LeaveStart AND LeaveEnd ;

Then you can open that as a recordset and read the count of conflicting records. If you built a named query for this purpose, you could also run a DCount on it.

Without more information about your setup, though, it would be hard for us to advise on the best approach.

 

[Pat Hartman]

You actually need two tests. One to see if the start date is in an existing range and the second to see if the end date is in an existing range.