Visual Basic- Access - Need help wiht programming

Question

I have a database with the following structure:

IDNO - Text(7), From Date - Date, To Data - Date, Year - Number, Full_Part - Logical.

I want to take out the missing date ranges for each IDNO for each particular year. ie.. I need to take out the year wise gaps for records. also, if the entire year is missing then the gap will be the entire year with the full_part field in the result showing F. so say.. in this example.. i need the program to return the result for

IDNO From Date To Date Year FULL_PART
A247108 01-Apr-02 30-Sep-02 2002 P
and
A247111 01-Jul-99 30-Sep-99 1999 P
A247111 01-Jan-00 31-Mar-00 1999 P (year is 1999 because it falls in the 1999-2000 financial year)
A sample database is appended below :-
IDNO From Date To Date Year FULL_PART
A247108 01-Apr-99 31-Mar-00 1999 F
A247108 01-Apr-00 31-Mar-01 2000 F
A247108 01-Apr-01 31-Mar-02 2001 F
A247108 01-Oct-02 31-Mar-03 2002 F
A247108 01-Apr-03 31-Mar-04 2003 F
A247111 01-Apr-00 31-Mar-01 2000 F
A247111 01-Apr-01 31-Mar-02 2001 F
A247111 01-Apr-02 31-Mar-03 2002 F
A247111 01-Apr-03 31-Mar-04 2003 F
A247111 01-Apr-99 30-Jun-99 1999 P
A247111 01-Oct-99 31-Dec-99 1999 P
A247124 01-Apr-99 31-Mar-00 1999 F
A247124 01-Apr-00 31-Mar-01 2000 F
A247124 01-Apr-01 31-Mar-02 2001 F
A247124 01-Apr-02 31-Mar-03 2002 F

Prasanth Moothedath · Answer

Well this isn't working.. simple because.. this will only return the row prior to the gap.. whereas in the result set.. i want the gap to be displayed.. its not in the recordset.. i hope i made u understand.. i think the sample database that i gave..and the result that i want is self-explanatory.. i actually want the gap between two dates ie to_date and the next from_date. and so on.. the program need to evaluate this only if the gap is more than 90 days. but that is secondry.. first if we can take out the gap dates.. then the other can follow.. thats not an issue..

PJ Martins · Answer

Not sure if this is what you're trying to accomplish but try to add one day to the [To Date] to bump it to the next month and then take the difference in years to see if it makes up at least one full year as follows: select * from Table where datediff(y [From Date] dateadd(d 1 [To Date])) select * from Table where datediff(y [From Date] dateadd(d 1 [To Date])) >= 1 -- return full rows