Substitute for overly-long IIf statement?

[Captain Database ...!!]

I have a series of results given in ranges, which the powers-that-be want to represent with integers for statistical purposes. They suggested the midpoint of each range (e.g., "5" instead of "1 - 10") and yet, while my first inclination was an IIf statement, this would require an inadmissably long IIf expression of some forty-to-fifty IIf conditionss (as shown in by following, except with conjoined statements representing data in ten-unit ranges from zero to five hundred):

IIf([Fructification]="0 - 10",5)

If not possible by a single IIf statement, what is then-the most practical way of doing this?

[RuralGuy]

I would use a User Defined Function (UDF) in a standard module. Are these values text (strings) or numbers?

[Captain Database ...!!]

They are mostly in the form of "1 - 10," "10 - 20," "20 - 30," etc.. I am not tooo familiar with UDF, however - how do I go about it?

[RuralGuy]

A UDF is a Public Function created in a Standard Module and not in a code module for a form. In this case I'm going to recommend using a Select Case statement. I'll put together a stub of the Function for you and you can then finish it for the rest of the possible values.

[RuralGuy]

Paste all of this code in a new module and don't name the module the same as the function. Module1 would be fine.

Option Compare Database
Option Explicit
Public Function Fruct(strInput As String) As String
   Select Case strInput
      Case "1 - 10"
         Fruct = "5"
      Case "10 - 20"
         Fruct = "15"
      Case "20 - 30"
         Fruct = "25"
      Case Else
         '-- If no match
         Fruct = "0"
   End Select
   
End Function

You would invoke it in a query with:
NewFructification: =Fruct(Fructification)

[Captain Database ...!!]

Holy schmikies, Batman .. it worked ..!! One other thing tho' .. some of the data from an older system-of-measurement already comes in single integers. Is there a way to tell the function to leave these single numbers as-is, without creating a new case for each number (with the number of decimals involved .. that would take a while) ?

[RuralGuy]

Just look for it in your function.

Public Function Fruct(strInput As String) As String
   If InStr(1, strInput, "-") = 0 Then
      '-- no 'range' in this input. Just return the original value
      Fruct = strInput
   Else
      Select Case strInput
         Case "1 - 10"
            Fruct = "5"
         Case "10 - 20"
            Fruct = "15"
         Case "20 - 30"
            Fruct = "25"
         Case Else
            '-- If no match
            Fruct = "0"
      End Select
   End If
End Function

[Captain Database ...!!]

Would it be possible then to 'also' tell it to just "give the midpoint" where there is a range? I am a little unfamiliar with VBA language, but what you've suggested works for the single integers. Having a clause that automatically adjusted ranges (i.e., by giving a midpoint) would just save me a whole lot of time in having to manually enter a case for each range.

[Captain Database ...!!]

Thanks .. sure glad I got 'this' sorted out .. .. scurries off to read-up on VBA

[RuralGuy]

Are you saying that if you have a value like "11" then you want "15" returned because it is in the "10 - 20" range?

[Captain Database ...!!]

It wasn't that .. some values are given purely as single integers like 11, while others are given only as ranges "0 - 10," "10 - 20," etc. (these are from an older, less accurate instrument).

[boblarson]

Just to tie the two threads together (where in the referenced one below I gave a function which appears to work for the need here):
https://www.accessforums.net/access/...html#post66455

[RuralGuy]

It wasn't that .. some values are given purely as single integers like 11, while others are given only as ranges "0 - 10," "10 - 20," etc. (these are from an older, less accurate instrument).

So if you get 11 what do you want returned by the function?

[Captain Database ...!!]

If a single integer like 11, I would want it repeated in the column .. whereas, if a range like 10 - 20, I would want the midpoint (sorry for the delay, been away a few days). As is, the function in the above-linked thread works 'except' that integers with decimals, for some reason, return the wrong numbers (whereas those without repeat as expected).

[boblarson]

'except' that integers with decimals, for some reason, return the wrong numbers (whereas those without repeat as expected).

Integers don't have decimals. So, you mean NUMBERS with decimals. And are you using the function above or the function I did up in your other thread?