Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    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>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Lounger
    Join Date
    Apr 2001
    Location
    Stuttgart, Germany
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •