Groupon Property - Report

[shiznaw]

I'm stuck and I usually can find an answer, but I don't work with reports often. I need to ask the Pros here whether it's possible to group Area by the following:

Dim str as string

str = Left(Area Field, 2)

Select Case str
Case Is = "11", "12", "13"
Report Group Area = "Division 1"

Case is = "14", "15,", 16"
Report Group Area = "Division 2"

Case is = 'etc', 'etc'
Report Group Area = "Division X"
end select

 

[Ranman256]

yes, but not report, put it in the query. Let the report read off the query.
in the vb code, create a new module, and paste:

Then in the query in a blank field: ReportGroupArea ([Area Field])
and it will return your value.

 public function ReportGroupArea(byval pvAreaField)
 Dim str as string

 str = Left(pvAreaField, 2)

Select Case str
Case  "11", "12", "13"
ReportGroupArea = "Division 1"

Case  "14", "15", 16"
ReportGroupArea = "Division 2"

Case  'etc'
ReportGroupArea = "Division X"

 case else
 end select 
 end function

 

[CJ_London]

you should really story this as a separate field but in answer to you question you can use the switch function which is similar to the case code

switch(left(area,2) in ("11,"12",13"),"Division 1",left(area,2) in ("13,"14",15"),"Division 2", left....etc.)

if your numbers are grouped as shown in your example you could try

switch(area\100<=13,"Division 1",area\100<=16,"division 2",area....etc)

which should execute faster

note the \ not /

 

[shiznaw]

yes, but not report, put it in the query. Let the report read off the query.
in the vb code, create a new module, and paste:

Then in the query in a blank field: ReportGroupArea ([Area Field])
and it will return your value.

 public function ReportGroupArea(byval pvAreaField)
 Dim str as string

 str = Left(pvAreaField, 2)

Select Case str
Case  "11", "12", "13"
ReportGroupArea = "Division 1"

Case  "14", "15", 16"
ReportGroupArea = "Division 2"

Case  'etc'
ReportGroupArea = "Division X"

 case else
 end select 
 end function

Thanxs to Your Solution, the actual code below plus the Query Worked. I sincerely thank you for this.

Public Function ReportGroupArea(ByRef pvAreaField)
Dim str As String

str = Left(pvAreaField, 3)

Select Case str
    Case Is = "003", "005", "047"
    ReportGroupArea = "DOLLAR THRIFTY TRANCHE"

    Case Is = "026", "044", "028"
    ReportGroupArea = "HAWAII TRANCHE"

    Case Is = "084"
    ReportGroupArea = "HRC & HFC TRANCHE"
 
    Case Is <> "003", "005", "047", "026", "044", "028", "084" 'All Else is US
    ReportGroupArea = "US TRANCHE"
End Select
End Function