formula to long?

A

araza123

New member
Local time
Yesterday, 21:02
Joined
Apr 6, 2022
Messages
24
hi team.

I am trying to input basically a long if statement. I have 28 total scenario and after typing them out it seems as access cannot handle this many?

Here is the formula typed . Essentially based on the number they will belong to a certain quarter. I believe access lets be input up to Q6. Is there a better way to do this or a work around?

IIf(Val([Date Difference] & "")>2563,"Q28",IIf(Val([Date Difference] & "")>2471,"Q27",IIf(Val([Date Difference] & "")>2379,"Q26",IIf(Val([Date Difference] & "")>2287,"Q25 ",IIf(Val([Date Difference] & "")>2195,"Q24",IIf(Val([Date Difference] & "")>2103,"Q23",IIf(Val([Date Difference] & "")>2011,"Q22",IIf(Val([Date Difference] & "")>1919,"Q21",IIf(Val([Date Difference] & "")>1829,"Q20",IIf(Val([Date Difference] & "")>1737,"Q19",IIf(Val([Date Difference] & "")>1645,"Q18",IIf(Val([Date Difference] & "")>1553,"Q17",IIf(Val([Date Difference] & "")>1463,"Q16 ",IIf(Val([Date Difference] & "")>1371,"Q15",IIf(Val([Date Difference] & "")>1279,"Q14",IIf(Val([Date Difference] & "")>1187,"Q13",IIf(Val([Date Difference] & "")>1097,"Q12",IIf(Val([Date Difference] & "")>1005,"Q11", IIf(Val([Date Difference] & "")>913,"Q10 ",IIf(Val([Date Difference] & "")>821,"Q9",IIf(Val([Date Difference] & "")>731,"Q8",IIf(Val([Date Difference] & "")>639,"Q7",IIf(Val([Date Difference] & "")>547,"Q6",IIf(Val([Date Difference] & "")>455,"Q5", IIf(Val([Date Difference] & "")>365,"Q4",IIf(Val([Date Difference] & "")>273,"Q3",IIf(Val([Date Difference] & "")>181,"Q2",IIf(Val([Date Difference] & "")>90,"Q1"))))))))))))))))))))))))))))
 
If that doesn't work there is another method. Make a public function in a general module that uses a SELECT case to return whatever you wanted:

Code: 
Public Function SelectQuarter(  DDiff as Date ) As String

    SELECT CASE DDiff
'
        BETWEEN 91 AND 181
            SelectQuarter = "Q2"
        BETWEEN 182 AND 273
            SelectQuarter = "Q3"
        BETWEEEN....
'
    END SELECT
'
End Function

I'm not sure what happens because the math is suspicious but doing it this way, your SQL can call SelectQuarter with just the date difference and you will get back whatever is required. AND you can have a CASE ELSE to handle pathological cases with any special return that might be required. Though this might be a little slower to execute because of that change between SQL and VBA context, this will be more flexible and won't care how many quarters you wish to return. And you can tailor the input if your actual data isn't in DATE format, because before the SELECT CASE you can do a conversion if needed.
 
With so many periods you may well be better off recording them in a table, and joining the table to give the required period according to date. It gives you the flexibility to add new periods easily in the future without modifying code - just add a new record to the table.
 
You can calculate quarters from dates. There is no need for an unmanageable IIF() block or Select Case statement. Check out the DatePart() function with an interval of "q".
 
Here's a function that returns the number of quarters for a given date, starting at a benchmark year...
Code: 
Function GetQuarterSinceBenchmarkYear(d1 As Date, BenchmarkYear As Integer) As String
    GetQuarterSinceBenchmarkYear = "Q" & DatePart("q", d1) + 4 * (Year(d1) - BenchmarkYear)
End Function
 
  • Like
Reactions: JHB
In many situations like this, the more effective options tend to be one of two, IMO.

  • VBA functions such as suggested by MarkK and The_Doc_Man.
  • Table driven solutions that persist your time spans (i.e. the quarters) as records which can be joined into queries to supply the needed details.
 
cheekybuddha said:
With so many periods you may well be better off recording them in a table, and joining the table to give the required period according to date. It gives you the flexibility to add new periods easily in the future without modifying code - just add a new record to the table.
Click to expand...
GPGeorge said:
In many situations like this, the more effective options tend to be one of two, IMO.

  • VBA functions such as suggested by MarkK and The_Doc_Man.
  • Table driven solutions that persist your time spans (i.e. the quarters) as records which can be joined into queries to supply the needed details.
Click to expand...
Thanks guys, so i have a table recorded but am struggling on how i would join it. You see what I have right now is a query which has 2 dates and the difference between them. Based on what that returns I want to be able to put that number in a quarter. Not sure if that makes sense. Table and query below

1650393602275.png


1650393624029.png
 
First things first. Field names. Ouch. Let's get that out of the way. It's a problem to use field (and other object) names with non-standard characters in them. That includes spaces, which are undesirable. It also includes special characters like the octothorpe, or hash or pound sign. Definitely a bad choice. Unless you have so much invested already that correcting these problems would be unreasonable, it's a really important improvement to get rid of all that. I.e. Contract No --> ContractNo, and C#Contract ID -->CNoContractID or whatever that # is supposed to signify. Life will be much happier.

Next, what I had in mind for a table was more along the lines of a Quarter designation and two fields for "QuarterFirstDate" and "QuarterLastDate".

In other words, Q1 starts 1/1/2022 and ends 3/1/2022 (in US date format). Possibly Q1 2022/01/01 2022/03/01 to be more explicit. Q2 would then be 2022/4/1 to 2022/6/30 and so on. This allows you to specify that any given date in your transaction tables falls within a specific span of time and hence, categorized by the Quarter designation for that time span.

I strongly suspect there will be other proposals as well. Access developers are like that.
 
GPGeorge said:
First things first. Field names. Ouch. Let's get that out of the way. It's a problem to use field (and other object) names with non-standard characters in them. That includes spaces, which are undesirable. It also includes special characters like the octothorpe, or hash or pound sign. Definitely a bad choice. Unless you have so much invested already that correcting these problems would be unreasonable, it's a really important improvement to get rid of all that. I.e. Contract No --> ContractNo, and C#Contract ID -->CNoContractID or whatever that # is supposed to signify. Life will be much happier.

Next, what I had in mind for a table was more along the lines of a Quarter designation and two fields for "QuarterFirstDate" and "QuarterLastDate".

In other words, Q1 starts 1/1/2022 and ends 3/1/2022 (in US date format). Possibly Q1 2022/01/01 2022/03/01 to be more explicit. Q2 would then be 2022/4/1 to 2022/6/30 and so on. This allows you to specify that any given date in your transaction tables falls within a specific span of time and hence, categorized by the Quarter designation for that time span.

I strongly suspect there will be other proposals as well. Access developers are like that.
Click to expand...
Thanks for the advice, always open to any feedback. So in terms of the quarters issue. I guess to be clear its not actual quarters like wha
t you are referring to. Each of the contracts will have a date in which the sale was made and a claim was made, the difference of those 2 fields will tell us what quarter the car was in when a claim was filed. So for example if you a sale was made on 1/1/2022 and a claim was noted today, it would be in Q2 since the difference between 1/1/22 and 4/19/2022 is above 90 days but less 181 days.


Pat Hartman said:
Since the number of days in a year is different for leap years, that changes the quarter boundaries. Can you explain in words what you are looking for? Also, tell us what is wrong with an approximate number of quarters obtained by dividing by 91?
Click to expand...
Similar to what i noted above. Each of the contracts will have a date in which the sale was made and a claim was made, the difference of those 2 fields will tell us what quarter the car was in when a claim was filed. So for example if you a sale was made on 1/1/2022 and a claim was noted today, it would be in Q2 since the difference between 1/1/22 and 4/19/2022 is above 90 days but less 181 days.

not sure if this make sense but appreciate the help
 
Pat Hartman said:
Since the number of days in a year is different for leap years, that changes the quarter boundaries slightly but since you are spanning multiple years, the boundary changes affect all years following the leap year and that means you need to know what year the table started and how to continue the pattern. And that is the flaw with the IIf() and Select Case methods also.

Can you explain in words what you are looking for? Also, tell us what is wrong with an approximate number of quarters obtained by dividing by 91 which is the simplest way to get what you want? Since there are 365 days in a year, maybe using 91.25 would be better. But without knowing your objective, we don't have a generic suggestion.

I came up with this function but it doesn't return the same result as your nested IIf()'s but, as long as you round up, it should match the divide by 91.25 method.

The function is a little clunky because it is attempting to do non-base10 arithmetic. It is doing "quarter" arithmetic. 10 = ten in decimal. 10 = 8 in binary, 10 = 4 in quarters:)

Code: 
Public Function DifInQtrs(dt1 As Date, dt2 As Date) As Integer

    Dim iQtr            As Integer
    Dim iYear         As Integer
    Dim iYear1      As Integer
    Dim iQtr1         As Integer
    Dim iYear2      As Integer
    Dim iQtr2         As Integer
    Dim iResult     As Integer
    Dim DaysDif    As Integer
   
    If IsDate(dt1) Then
        If IsDate(dt2) Then
        Else
            DifInQtrs = -2      '' dt2 invalid
            Exit Function
        End If
    Else
        DifInQtrs = -1          ''dt1 invalid
        Exit Function
    End If
   
    If dt1 > dt2 Then
        DifInQtrs = -3      ''dt1 must be <= dt2
        Exit Function
    End If
   
    DaysDif = DateDiff("d", dt1, dt2)
   
    iYear1 = Year(dt1)
    iQtr1 = DatePart("q", dt1)
    iYear2 = Year(dt2)
    iQtr2 = DatePart("q", dt2)
   
    If iQtr2 < iQtr1 Then
        iQtr2 = iQtr2 + 4
        iYear2 = iYear2 - 1
    End If
   
    iQtr = iQtr2 - iQtr1
    iYear = iYear2 - iYear1
    iResult = (iYear * 4) + iQtr
   
    DifInQtrs = iResult
    Debug.Print DaysDif
End Function
Click to expand...
wow thank you so much, so im obviously very green here. This code is inputed into where? surely not the builder? Guessing into the SQL module? if so would this goto into a table? or querey?
 
To me, a quarter (timewise) means a specific quarter of the year eg Q3 2021 or Q2 2022. Is the real question, the number of quarters between two dates. So if the start date was in Q3 2021 and end date was in Q2 2022, then the number of quarters would be 3
 
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.
 
araza123 said:
I have 28 total scenario and after typing them out it seems as access cannot handle this many?
Click to expand...
the iif function allows you to have only 14 levels of occurrence, so implement 28 levels through a custom function on vba

although it is possible to divide 28 levels into 3 parts (2 may not be enough) and make a double attachment
 
or:

Quarter: DMin("Quarters","Quarters","[Days] >= " & ([Claim Entered Date] - [Sale Date KY]))
 
cheekybuddha said:
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.
Click to expand...
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
 
How about:
Code: 
Date_Difference: "Q" & (Int((GAP_Claims_new.[Claim Entered Date]-[Sale Date KY])/90)) + 1)
 
Thank you to everyone who assisted here. Really appreciate everyone trying to help out. I believe I have the solution!
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom