formula to long? (1 Viewer)

[araza123]

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"))))))))))))))))))))))))))))

 

[bastanu]

Maybe try using the Switch() function: https://support.microsoft.com/en-us/topic/d750c10d-0c8e-444c-9e63-f47504f9e379

Cheers,

 

[The_Doc_Man]

Look into the SWITCH statement.

Switch Function - Microsoft Support

Use the Switch function in Acess to evaluate a list of expressions.

support.microsoft.com

 

[The_Doc_Man]

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:

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.

 

[cheekybuddha]

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.

 

[MarkK]

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".

 

[MarkK]

Here's a function that returns the number of quarters for a given date, starting at a benchmark year...

Function GetQuarterSinceBenchmarkYear(d1 As Date, BenchmarkYear As Integer) As String
    GetQuarterSinceBenchmarkYear = "Q" & DatePart("q", d1) + 4 * (Year(d1) - BenchmarkYear)
End Function

 

[GPGeorge]

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.

 

[Pat Hartman]

Table based solutions are far easier to understand and modify if necessary. That would be my first choice. But this looks like a calculation issue so I would go with a function that calculates the number of days between the two dates and then converts the difference into a quarter number. I didn't test it but @MarkK posted one solution. If that gives you what you expect, use it. Otherwise, we can work on refining the calculation to take into account the starting quarter in the BenchmarkYear. The problem is more like the age problem. Your age depends on whether the current date is in the months before or after your birth month.

 

[araza123]

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.

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.

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

 

[GPGeorge]

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.

 

[Pat Hartman]

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

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

 

[araza123]

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.

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.

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?

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

 

[araza123]

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

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

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?

 

[Pat Hartman]

How far into the past do you need to go? How precise does the result have to be? How do you feel about updating your table every month to shift the effect of leap year if you go the hard-coded method? Test my function with your data and compare it to the results of your IIF() and the function suggested by MarkK and the simple divide by 91 method. You might need to add 1 to the result if you use the divide by 91 method and then truncate rather than always rounding up.

Anyway, you now have multiple potential solutions. Only you have the data and business knowledge to assess the results.

Create the two suggested functions (the select case would work just like your nested IIf() and have the same problem) so you don't need to create it unless that is the method you want to choose. Then create a query that pulls your own data and add a column to the query to use each different method. You can look at the results where the methods return different answers and decide which of them is what you want or if you still need something else.

 

[Cronk]

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

 

[Pat Hartman]

To put the code somewhere, create a new standard module - not a class module. Name the module "mFunctions" or something else. Anything except the name of the three functions you are going to put there. Copy my code and MarkK's code into the new module. Create a third function if you want to try the divide by 91 method.

Here's the divide by 91 method.

Don't forget to comment out the debug.print statements in both my functions. You don't want them to execute when you are running the function in your query.

Here's a link to the VBA arithmetic operators help entry just in case you don't know what /, \, and mod are

Arithmetic Operators in VBA

VBA arithmetic operators - symbols that instruct the compiler to perform basic math (add, divide, powers, modulus, etc.) - are explored herein.

masterofficevba.com

Function DivideBy91(dt1 As Date, dt2 As Date) As Integer
    Dim DaysDif    As Integer
    Dim dQtrs            As Double
    Dim iQtrs           As Integer
    Dim DifInQtrs   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)
    If DaysDif Mod 91 > 0 Then
        iQtrs = (DaysDif \ 91) + 1
    Else
        iQtrs = DaysDif / 91
    End If
    
    DivideBy91 = iQtrs
    Debug.Print DaysDif
    
End Function

 

[Pat Hartman]

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

That's why I created the function to do "quarter" arithmetic. I don't have a large enough dataset nor tine inclination to spend anymore time on this to create one so I'm leaving it in the hands of the poster to actually compare the results of the four options to see which one gets what he thinks is the correct answer on the "margins".

ALWAYS make sure your test data contains data on the margins. That is how I discovered the Y2K problem in 1972. My client's mortgages had 30 year horizons so I discovered what happened when 2000 was treated as a leap year when for some reason, I happened to know it shouldn't be I always find it amazing when some random facts just jump out from memory. I owe a lot of them to my 8th grade math teacher who also taught us how to do tax returns and balance a checkbook. And my 6th grade history teacher who was a Renaissance Woman and instilled in me a very strong love for maps and geography. You want me on your team if you are playing Trivial Pursuit and you get a Geography question.

 

[cheekybuddha]

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.

 

[SHANEMAC51]

I have 28 total scenario and after typing them out it seems as access cannot handle this many?

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