think i was very much overthinking this, i think taking the date diference and simply divding it up 90 should work but i would need to round up.You can also try:
SQL:SELECT c.[Claim Entered Date], c.[Sale Date KY], ( SELECT TOP 1 q.Quarters FROM Quarters q WHERE q.Days > ([c.Claim Entered Date] - c.[Sale Date KY]) ) AS Quarter FROM GAP_Claims_new c ;
If your Quarters Table had a FromDays and a ToDays, you could use a join rather than the subquery field.
Date_Difference: "Q" & (Int((GAP_Claims_new.[Claim Entered Date]-[Sale Date KY])/90)) + 1)
appreciate you! it worked!!How about:
Code:Date_Difference: "Q" & (Int((GAP_Claims_new.[Claim Entered Date]-[Sale Date KY])/90)) + 1)