Visual Basic- Access - Need help wiht programming

Prasanth Moothedath

16y2.6k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasanth Moothedath16y ago
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 Martins16y ago
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])) < 1 -- return partial rows
select * from Table where datediff(y, [From Date], dateadd(d, 1, [To Date])) >= 1 -- return full rows