formula to long? (1 Viewer)

[arnelgp]

or:

Quarter: DMin("Quarters","Quarters","[Days] >= " & ([Claim Entered Date] - [Sale Date KY]))

 

[Pat Hartman]

@araza123,
If you can't build the query, then post a database or spreadsheet with data you want to analyze. We don't need anything except the recordID and some start and end dates.

 

[araza123]

You can also try:

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.

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.

What would be the best approach to have this all in one field. Right now I have my date difference calc as Date_Difference: GAP_Claims_new.[Claim Entered Date]-[Sale Date KY].

if i wanted to add the then divide by 90 and round up, how would i do that

something like Date_Difference: GAP_Claims_new.[Claim Entered Date]-[Sale Date KY]/(90) with it rounding up

 

[cheekybuddha]

How about:

Date_Difference: "Q" & (Int((GAP_Claims_new.[Claim Entered Date]-[Sale Date KY])/90)) + 1)

 

[araza123]

Thank you to everyone who assisted here. Really appreciate everyone trying to help out. I believe I have the solution!

 

[araza123]

How about:

Date_Difference: "Q" & (Int((GAP_Claims_new.[Claim Entered Date]-[Sale Date KY])/90)) + 1)

appreciate you! it worked!!

 

[cheekybuddha]