Date Problem

[Bryan Hughes]

Hello,

I have a query that searches for the last date meeting a certain criteria.
This query works fine.

' EED = Ending Eligibility Date
' LstCAD = Last Case File Activity Date
' CAS = Activity Status

mstrSQL = "SELECT tblGT_Case_File.GTFID, "
mstrSQL = mstrSQL & "CFA.LstCAD AS CAD, CFA.CAS, tblGT_Case_File.CFID,
tblGT_Case_File.CM, "
mstrSQL = mstrSQL & "tblGT_Case_File.EED, tblGT_Case_File.IOD,
tblGT_Case_File.CDID "
mstrSQL = mstrSQL & "FROM tblGT_Case_File "
mstrSQL = mstrSQL & "INNER JOIN (SELECT GTFID, "
mstrSQL = mstrSQL & "MAX([CAD]) AS LstCAD, CAS "
mstrSQL = mstrSQL & "FROM tblGT_Case_File_Monthly_Activity_Status "
mstrSQL = mstrSQL & "WHERE CAS = True "
mstrSQL = mstrSQL & "GROUP BY GTFID, CAS) AS CFA "
mstrSQL = mstrSQL & "ON tblGT_Case_File.GTFID = CFA.GTFID "
mstrSQL = mstrSQL & "WHERE tblGT_Case_File.Open = True And CFA.LstCAD <
tblGT_Case_File.EED "
mstrSQL = mstrSQL & "And MONTH(CFA.LstCAD) =" & intMonth & ";"

Now I need to create a query that will find the total consecutive months for
the current Activity Status.


Basicly
Case Files Can be Active or Inactive for a period of several months or more.
If Case File 1 is currently active for this month, then I need to find the
total number for consecutive months that this case file has been active
during the
current active period.

I can't get my head around it.

-TFTH
Bryan

 

[Bryan Hughes]

I figured out the problem. The simple ones are always the hardest ones to
see.

I just queried for the last Date for Inactive Status (if currently active)
then did datediff.

If nothing found then just must be new case file so just find datediff from
date opened.

-TFTH
Bryan