Can i simplify my Select Case (Access 2000)

Topic

New Reply

Can i simplify my Select Case

My question is can i simplify my code since it seems too lengthy to me.
I have two option group controls, the first is called months and

consist of the 12 options( the months). The second option group is

called office and consist of 10 offices.My code allows either to open a

report without choosing the second option group, or chosing a month and

the office.It is fine, however it is too lengthry, i have to write the

12 select cases of the months, and then i must insert the ten

possibilties of the offices.Is there a way to make my code more

compact?
i will quote only the select cases for the first two months:
Select Case month
Case 1
If office = “” Then
strCriteria = jan
ElseIf office = 1 Then
strCriteria = jan & strLondon
ElseIf office = 2 Then
strCriteria = jan & strLiverpool
ElseIf office = 3 Then
strCriteria = jan & strManchester
ElseIf office = 4 Then
strCriteria = jan & strBerlin
ElseIf office = 5 Then
strCriteria = jan & strRome
ElseIf office = 6 Then
strCriteria = jan & strWien

Case 2
If ofice = “” Then
strCriteria = feb
ElseIf office = 1 Then
strCriteria = feb & strLondon
ElseIf office = 2 Then
strCriteria = feb & strLiverpoll
ElseIf office = 3 Then
strCriteria = feb & strManshester
ElseIf office = 4 Then
strCriteria = feb & strBerlin
ElseIf office = 5 Then
strCriteria = feb & strRome
ElseIf office = 6 Then
strCriteria = feb & strWien

Reply | Quote

Replies

Put your office selection with a select case or with if … elseif … in a separate function and call this function in each case of your select case for your month.

Francois

Reply | Quote

Consider putting your strings in an array and cycle thru them with a ‘for’ loop.

Reply | Quote

You didn’t give enough code to figure out exactly what you are doing. From what you did give, it looks like it could be simplified to something like this:

     If Office = "" Then
        strCriteria = Jan
    Else
        strCriteria = Jan & Array("London", "Liverpool", "Manchester", _
          "Berlin", "Rome", "Wien")(Office - 1)

Depending on what the other code does, the cases may be handled by something similar.

The above code works in Excel. It might have to be slightly different in Access.

Reply | Quote

Try this:

Dim strMonth As String  'chosen month
Dim strOffice As String           'chosen office
Dim strCriteria As String        'result

'determine the month from the month option group
strMonth = Choose([grpMonth], "Jan", "Feb","Mar", "Apr","May", _
      "Jun", "Jul", "Aug", "Sep", "Oct","Nov", "Dec")

'determine the office from the office option group
strOffice = Choose([grpOffice],strLondon, strLiverpool, strManchester, _
    strBerlin, strRome, strWien)

'populate strCriteria 
strCriteria = strMonth & strOffice

This assumes that your option groups are named grpMonth and grpOffice. I would recommend that you avoid Month as a field or control name since it is also the name of a built in function in VBA and that could cause you problems.

Reply | Quote

I’m having trouble understanding what you are doing. For example, what is in the strings “jan” and “strLondon”?

It seems you want to open your report with a WHERE statement and includes selection criteria for either/both/neither of month and office. This should be alot easier than what you are doing. First of all, I’d use a combo box for each, since you can only make one selection anyway. code would look like this (assumes both combo boxes have a numeric field as bound column:

dim strWHERE as string
dim strMonth as string
dim strOffice as string
dim strAnd as string

if not isNull(cboMonth) then
   strMonth = "[monthfield] = " & cboMonth
end if

if not isnull(cboOffice) then
  strOffice = "[officefield]=" & cboOffice
end if

if not isnull(cboMonth) and not isnull(cboOffice) then
  strMonth = " AND "
end if

strWhere = strMonth & strAnd & strOffice

Reply | Quote

Hi,

'Define 2 arrays to hold all possible choices
'(define index starting with 1
'to correspond to option values)
Dim astrMonth(1 To 12) As String
Dim astrOffice(1 To 10) As String
 
'Fill in month names,
'index is the ordinal no. of the month
astrMonth(1)="Jan"
[fill in Feb, Mar, ...]
astrMonth(12)="Dec"
 
'Fill in office names
astrOffice(1)="London"
[fill in Liverpool, Manchester...]
astrOffice(10)="Paris"
 
'Take the chosen month and concatenate
'with chosen office
'If no office chosen, take only the month
If office="" Then
   strCriteria=astrMonth(month)
Else
   strCriteria=astrMonth(month) & astrOffice(office)
'You may want to put a blank between the two strings
End If

However, Mark’s solution with combo boxes would be better, specially if there is a chance the office locations change some time in the future.

Reply | Quote