Case Statement Help Please

[graviz]

I have a function that converts a number of days field into 30 day buckets. I've been asked to keep increasing the number of buckets so I was wondering if there was a way to do it with a loop of some sort. It would always be in increments of 30 and it could say either "60 Days" or "2 Months" as an example. Any idea?

Function Days_Buckets(days_between As String) As String
        
        Dim NN As Integer
        NN = CInt(days_between)
        
        Select Case NN
        
        Case 1 To 30
            Days_Buckets = "A_0-30 Days"
        Case 31 To 60
            Days_Buckets = "B_31-60 Days"
        Case 61 To 90
            Days_Buckets = "C_61-90 Days"
        Case 91 To 120
            Days_Buckets = "D_91-120 Days"
        Case 121 To 150
            Days_Buckets = "E_121-150 Days"
        Case 151 To 180
            Days_Buckets = "F_151-180 Days"
        Case 181 To 210
            Days_Buckets = "G_181-210 Days"
        Case 211 To 240
            Days_Buckets = "H_211-240 Days"
        Case 241 To 270
            Days_Buckets = "I_241-270 Days"
        Case 271 To 300
            Days_Buckets = "J_271-300 Days"
        Case 301 To 330
            Days_Buckets = "K_301-330 Days"
        Case 331 To 360
            Days_Buckets = "L_331-360 Days"
         Case 361 To 390
            Days_Buckets = "M_361-390 Days"
         Case 391 To 420
            Days_Buckets = "N_391-420 Days"
         Case 421 To 450
            Days_Buckets = "O_421-450 Days"
         Case 451 To 480
            Days_Buckets = "P_451-480 Days"
         Case 481 To 510
            Days_Buckets = "Q_481-510 Days"
         Case 511 To 540
            Days_Buckets = "R_511-540 Days"
        Case Else
            Days_Buckets = "S_Over 540"
            
        End Select
    End Function

 

[Cronk]

What happens after "Z_..Days"?

 

[David R]

What is the 'bucket' then used for? Buckets = Int((NN+29)/30) would be simpler, perhaps...

 

[graviz]

What happens after "Z_..Days"?

The letter was just to keep the buckets in numerical order. That's also where I'm not quite sure on what to do.

 

[jdraw]

Is there some sort of business issue behind this request? It would seem to me that a Date field, especially since you're working with days, would be a good approach.
The DateAdd function would seem to cover off any arithmetic. And Year and Month functions could get to specifics, but we really don't know the issue involved, yet.

 

[Cronk]

Graviz, that reminds when I asked the manager at an organisation which had engaged me to develop an application about what reports he required. The response was, "I don't know, whatever I might want at the time" Duh!!

However, the following should give you what you want, up to input of just over 32,000

Function DaysRange(intDays as integer) As String
   Dim n As Integer
   
   n = (intDays - 1) \ 30
   If n < 26 Then
      DaysRange = Chr(65 + n) & "_" & (n * 30 + IIf(n > 0, 1, 0)) & "-" & ((n + 1) * 30) & " Days"
   Else
      DaysRange = "[User yet to decide what he wants here]" & "_" & (n * 30 + 1) & "-" & ((n + 1) * 30) & " Days"
   End If
End Function

 

[graviz]

What is the 'bucket' then used for? Buckets = Int((NN+29)/30) would be simpler, perhaps...

Any way to make it in numerical order? (i.e. 3 digit number format) The issue is it goes 0, 1, 10, 100, 101 and so on.

 

[David R]

WHAT "goes 0, 1, 10, 100, 101 and so on"? That calculation I gave you will just spit out a numerical value for how many ~Months have happened... though you'll want to recast Days_Buckets as an Integer, not String. That may be why it's sorting funny.

You still haven't said what business purpose this NN/30 is for.

 

[graviz]

WHAT "goes 0, 1, 10, 100, 101 and so on"? That calculation I gave you will just spit out a numerical value for how many ~Months have happened... though you'll want to recast Days_Buckets as an Integer, not String. That may be why it's sorting funny.

You still haven't said what business purpose this NN/30 is for.

The purpose is to group how old accounts are. Because some may be 20+ years old doing it by day would give too many data points.

Once I run a query that the order it displays if i do ascending. I would like it to go 0,1,2,3 and so on. Does that make sense?

 

[David R]

The purpose is to group how old accounts are. Because some may be 20+ years old doing it by day would give too many data points.

Once I run a query that the order it displays if i do ascending. I would like it to go 0,1,2,3 and so on. Does that make sense?

I think you're making your life harder than it has to be. A report can group things by month automatically. My calculation will give you an arbitrary number of 'buckets', and can be included directly in your query as a calculated field:

DaysRange: Int((NN+29)/30)

.

But you still haven't answered why it is showing up as "01", and I think it has to do with

Function DaysRange(intDays as integer) [B]As String[/B]

 

[graviz]

I think you're making your life harder than it has to be. A report can group things by month automatically. My calculation will give you an arbitrary number of 'buckets', and can be included directly in your query as a calculated field:

DaysRange: Int((NN+29)/30)

.

But you still haven't answered why it is showing up as "01", and I think it has to do with

Function DaysRange(intDays as integer) [B]As String[/B]

Thanks everyone! I think I was making it harder than it needed to be. I added the right function into the suggested solution and it gave me the months in the correct ascending order.

Months: Right("00" & Fix((([Days])+29)/30),3) & " Months"