# Thread: Can i simplify my Select Case (Access 2000)

1. ## Can i simplify my Select Case (Access 2000)

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

2. ## Re: Can i simplify my Select Case (Access 2000)

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

3. ## Re: Can i simplify my Select Case (Access 2000)

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

4. ## Re: Can i simplify my Select Case (Access 2000)

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:

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

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.

5. ## Re: Can i simplify my Select Case (Access 2000)

Try this:

<pre>Dim strMonth As String <font color=448800>'chosen month</font color=448800>
Dim strOffice As String <font color=448800>'chosen office</font color=448800>
Dim strCriteria As String <font color=448800>'result</font color=448800>

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

<font color=448800>'determine the office from the office option group</font color=448800>
strOffice = Choose([grpOffice],strLondon, strLiverpool, strManchester, _
strBerlin, strRome, strWien)

<font color=448800>'populate strCriteria </font color=448800>
strCriteria = strMonth & strOffice</pre>

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.

6. ## Re: Can i simplify my Select Case (Access 2000)

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:
<pre>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
</pre>

7. ## Re: Can i simplify my Select Case (Access 2000)

Hi,

<pre>'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</pre>

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•