Make Sure All Date Ranges Are Consecutive 3

[TJLearn]

Hello,

Can anyone help me out please.
I have some records as below:
RecordID StartDate EndDate
1 1/1/01 4/4/01
2 5/5/01 8/10/01
3 8/11/01 11/10/03
4 11/11/03 12/2/03

These 4 records were added in tblStates table. However, the user goes ahead and delete record #2 and #3 and add a new record with StartDate from 5/5/01 and EndDate to 11/10/02. In this case the user is supposed to type in 11/10/03 for the EndDate for this new record because there cannot be a gap between the date ranges of the records. I am not sure how to trap the user error to make sure the StartDate and EndDate are typed in correctly without gaps between date ranges.

Your help is very appreciated.

 

[Eupher]

Your question assumes there will only ever be one intervening record. Is this correct? For instance, in your scenario the user deletes records 2 and 3, leaving a date gap between 5/4/01 (I'm assuming 4/4/01 is a typo) and 11/11/03. And you want to ensure that any subsequent entry entirely fills the date gap. What if the user deletes records 2 and 3, but wants to replace them with two entries, not just one?

Ken S.

 

[TJLearn]

Hi Eupher,

Yes, 4/4/01 is a typo, it should be 5/4/01. Thanks for letting me know. The records now should be like:

RecordID StartDate EndDate
1 1/1/01 5/4/01
2 5/5/01 8/10/01
3 8/11/01 11/10/03
4 11/11/03 12/2/03

To your question "if the user deletes records 2 and 3, but wants to replace them with two entries, not just one?", my answer is they have to delete all the records ever after the maximum EndDate of the deleted records (11/10/03). In this case, they have to delete record #4 before adding more then 1 records.

Thanks!

 

[TonyJollans]

Hi TJLearn,

Why are you trying to hold the End Date? You always know what it should be and you are just creating work validating that the User types it in correctly. Why not just code to assume that one date range ends immediately before the next one begins?

Enjoy,
Tony

 

[OhioSteve]

I'm not entirely clear about what you want to do. In your first post, you say that the rule is "there cannot be a gap". Its hard for me to understand your second post. But perhaps there you mean that the user cannot create a gap by deleting an intermediate record.

I am a big advocate of using SQL to solve problems. But in this case I suspect that you need to use VB to fix this. You need to write three VB functions, "deleteOK", "addOK", and "changeOK". Each of them will read in two arrays, one for the enddates and one for the startdates. It will also read in the proposed delete, add, or change. Then it will decide if the action is valid and return a logical value.

 

[TJLearn]

Hi Eupher and OhioSteve,

Thanks a lot for your imputs that help me make it work already.