checking overlapping data

[chanu]

I have a table tblAllowance.The design is as follows. But the data shown
here is from a continuous form (which is a subform) limited to a particular
allowance. The new data is entered from the main form’s unbound controls
AllowanceName FromDate ToDate AllowanceRate
House Rent 01/01/2009 31/03/2009 7385
House Rent 01/05/2009 30/11/2009 7570
HouseRent 01/12/2009 7970
The null value indicates that it is the present rate and we don’t know how
long it lasts.
I actually want three validation rules on this to check data overlapping.
Because there is no guarantee that the user enters sequentially.
1. To see either Me.parent.txtFromDate or Me.parent.txtToDate (these are
the unbound controls of the main form) falls between rst!FromDate and
rst!ToDate
For ex: a. 01-01-2009 to 31-10-2009
b.01-11-2008 to 31-01-2009
c.01-02-2009 to 30-11-2009
2. But to test against a NULL value (in this example it is the last
row)
For ex: a. 01-12-2009 to 31-12-2009
b. 01-04-2009 to 31-12-2009
c.01-12-2008 to 01-12-2009
3. To go beyond the period.
For ex: 01-02-2010 to Null i.e. PresentDay
Here we have to make sure whether the last rate(i.e.from01-12-2009) stops
before this day(31-01-2010) or cancel it
The code I have written for this is:
Dim strSQL
strSQL = //SQL code on tblAllowance and WHERE AllowanceName=Me.cboAllowance
rst = currentdb.openrecordset(strSQL)
‘to test the first criteria
If rst.FindFirst â€FromDate <= #†& Me.txtFromDate & “# ToDate >= #†&
Me.txtFromDate & “#â€
//msgbox your data overlaps with rst!FromDate and rst!ToDAte
ElseIf rst.FindFirst â€FromDate <= #†& Me.txtToDate & “# ToDate >= #†&
Me.txtToDate & “#â€
//msgbox your data overlaps with rst!FromDate and rst!ToDAte
ElseIf rst.findFirst “ToDate = “ Null
‘to test 2.b,2.c
If rstFindFirst “FromDate >=#†& me.txtFromDate#& †and FromDate <= #“
&me.txtToDate&â€#â€
//Error message
‘to test 2.a
Elseif rstFindFirst “FromDate =#â€&me.txtFromDate&â€#â€
//whether the user wants to replace the last row in the form.
‘to test for 3.a
Elseif rstFindFirst “FromDate < # â€&me.txtFromDate & â€# â€
//alert the user about the gap
End if
End if
***here starts my code to copy the values into the recordset and requery the
form. But the code is not working properly. Can one tell me how to deal with
this problem and propose corrections in the code. Any suggestion will be
greatly appreciated.one request: being not well.versed in programming I could
not understand very well.Please answer my follow.up questions if I could not
understand the proposed correction

 

[Stefan Hoffmann]

hi,

I have a table tblAllowance.The design is as follows. But the data shown
here is from a continuous form (which is a subform) limited to a particular
allowance. The new data is entered from the main form’s unbound controls
AllowanceName FromDate ToDate AllowanceRate
House Rent 01/01/2009 31/03/2009 7385
House Rent 01/05/2009 30/11/2009 7570
HouseRent 01/12/2009 7970
The null value indicates that it is the present rate and we don’t know how
long it lasts.
I actually want three validation rules on this to check data overlapping.

Use an unique index to avoid identity on your date ranges. Then there
are three cases to examine left:

1. the interval A lies within the boundaries of interval B
2. the start date of A lies within the boundaries of interval B
3. the end date of A lies within the boundaries of interval B

SELECT Count(*)
FROM [tblAllowance] T
WHERE T.[FromDate] BETWEEN txtFromDate AND txtToDate ' case 1
OR txtFromDate BETWEEN T.[FromDate] AND T.[EndDate] ' case 2
OR txtToDate BETWEEN T.[FromDate] AND T.[EndDate] ' case 3

Untested, but this single query should be sufficent.

mfG
--> stefan <--

 

[Access infant]

Thanks a lot stefan,
This delayed thanks is because i could not understand what you said here.
Finally when i understand it now how can i stop myself from expressing thanks.
--
from
chanakya
Baruva

hi,

I have a table tblAllowance.The design is as follows. But the data shown
here is from a continuous form (which is a subform) limited to a particular
allowance. The new data is entered from the main form’s unbound controls
AllowanceName FromDate ToDate AllowanceRate
House Rent 01/01/2009 31/03/2009 7385
House Rent 01/05/2009 30/11/2009 7570
HouseRent 01/12/2009 7970
The null value indicates that it is the present rate and we don’t know how
long it lasts.
I actually want three validation rules on this to check data overlapping.

Use an unique index to avoid identity on your date ranges. Then there
are three cases to examine left:

1. the interval A lies within the boundaries of interval B
2. the start date of A lies within the boundaries of interval B
3. the end date of A lies within the boundaries of interval B

SELECT Count(*)
FROM [tblAllowance] T
WHERE T.[FromDate] BETWEEN txtFromDate AND txtToDate ' case 1
OR txtFromDate BETWEEN T.[FromDate] AND T.[EndDate] ' case 2
OR txtToDate BETWEEN T.[FromDate] AND T.[EndDate] ' case 3

Untested, but this single query should be sufficent.

mfG
--> stefan <--
.