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

    Select case (Access 2000)

    I have a select case for chosing the customer ans hence the orders :.

    Select Case Forms![Frm1]![Customers]
    Case 1
    strOffice = "AND ((customers.MEK)=1))"
    Case 2
    strOffice = "AND ((customers.MEK)=2))"
    Case 3
    strOffice = "AND ((customers.MEK)=3))"
    Case 4
    strOffice = "AND ((customers.MEK)=4))"
    Case 5
    strOffice = "AND ((customers.MEK)=5))"
    Case 6
    strOffice = "AND ((customers.MEK)=6))"
    End Select

    I want to have the option to open all the orders for all the customers provided that the user has not chosen an option in the Option

    I have tried with the following
    Case Else
    StrOffice = "AND ((customers.MEK)=1 Or 2 Or 3)
    I have also tried the folowing
    Case Else
    StrOffice = "AND ((customers.MEK)> 1))

    However in both cases i receive an error.
    Is ther any way in Access to do it?

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Richland, Washington, USA
    Thanked 0 Times in 0 Posts

    Re: Select case (Access 2000)

    It appears that that your strOffice variable will be used as a subclause for the WHERE clause of an SQL statement to select your customers. Is this correct?

    If so, it seems that if no option in the option group is chosen then all you need to do is leave this subclause off the SQL statement completely; i.e., strOffice = "".

    By the way, a more concise way to assign strOffice would be to do something like this:

    <font face="Georgia">strOffice = iif(isnull(Forms![Frm1]![Customers]), "", _
    "AND customers.MEK=" & Forms![Frm1]![Customers])
    </font face=georgia>

    Hope this helps.

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

    Re: Select case (Access 2000)

    The form of your code is ok in your Case Else, so the VBA compiler doesnt object. Something other than the compiler is giving you an error message; who is that?

    I would assume your first attempt would have to be written
    "AND ((customers.MEK=1) Or (customers.MEK=2) Or (customers.MEK=3))"
    but I am ignorant as to why your second approach doesnt work. Maybe too many spaces?

Posting Permissions

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