calculate date diff from 2 fields and multiple records (1 Viewer)

[bwc]

I am new to Access and to this forum. i apologize if this should have been posted under "Querys". i have been trying to create a query or VBA that will calculate time in service from the following table. this table consists of 10,000 records. strID separates time in service between members.

i need to calculate in what state a member has served in for a continuous 3 years. each record displays no more then 1 year. in this piece of the table shows that member 1 has served in "MN" from 9/16/1995 to 9/25/1998. how do i do this? will a query calculate this for me or do i need a VBA function?

strID strStatus strState datStartDate datEndDate
1 B1 VT 9/26/1991 9/11/1992
1 B1 MN* 9/12/1992 9/25/1992
1 B1 MN* 9/26/1992 8/25/1993
1 D2 8/26/1993 9/25/1993
1 D2 9/26/1993 9/25/1994
1 D2 9/26/1994 9/15/1995
1 B1 MN* 9/16/1995 9/25/1995
1 B1 MN* 9/26/1995 9/25/1996
1 B1 MN* 9/26/1996 9/25/1997
1 B1 MN 9/26/1997 9/25/1998
2 D1 11/26/1989 2/9/1990
2 H3 2/10/1990 5/24/1990
2 B1 5/25/1990 7/1/1990


thank you,

bryan

 

[aziz rasul]

Have you tried searching for the syntax of the DateDiff function from a internet search engine?

 

[Brianwarnock]

The syntax can also be found in VBA help, although the function can be used in queries.

Will consecutive entries for the same status and state always give continuous dates?

Brian

 

[bwc]

Will consecutive entries for the same status and state always give continuous dates?

the dates will always be continuous. the state and status may change...

 

[Brianwarnock]

This is going to need VBA code to read through the data storing information from the first of an ID/STATE combination whilst reading to the last to do the arithmetic, I'm sorry but I don't have time this weekend.

Brian