Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to write an array? (Access 2000)

    How to write an array?
    I want to perfrom an action only when a certain customer number is chosen in the atabase.Unfortunately the customer numbers do not follow any logical order, rather they are numbered after the postal code of the city.So my customer numbers are:
    123,9000,2700,6300,5000 and 7700.

    I would like to write a code something like that:
    Dim CustomerId as Control
    Set customerID = Forms![FOrderInformation]![Customerid]
    If CustomerId = 123 Or 900 Or 6300 or 5000 Or 770 Then
    Dim message, title, default, myvalue
    message = "enter Auftrag Number, please " 'set prompt
    end if

    However somehow Acces cannot recoginze my numbers.Is there any more bulletproof method as Array,
    or perhaps i am mistaken with my code?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to write an array? (Access 2000)

    Why not store the relevent customer numbers in a table, then count the number of records returned when that table is queried with the active customer number as a criterion? if no records are returned, then the active customer is not one of the specified customers where special action must be taken. If the query returns a result, then the active customer is in the set of 'special customers' and you can display the massage and take appropriate actions.

    This should also make it easier to maintain the special customer numbers, I would think.

    If you want to hard-code the numbers into VBA as you are showing, I think the syntax for the "OR" operator is to make disjunctions between two expressions - you would have to use something like:
    <pre>If(((((custID=123 OR custID=9000) OR custID=2700) OR custID=6300) OR _
    custID=5000) OR custID=770) then...
    </pre>


    (I may have the brackets wrong - this is on-the-fly) The intent is to chain the "OR" operators so that "(test1 OR test2) is presented as a single truth value (call it R1) to the next "OR" as "(R1 OR test3)" - call that R2 - and present it to the next "OR" as "(R2 OR test4)" and so on...

    It would probably be easier to set this up as a CASE statement:
    <pre>Select Case CustID
    Case 123
    Msg routine
    Case 9000
    Msg routine
    ...
    Case Else
    continue
    End Select
    </pre>


  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to write an array? (Access 2000)

    You don't need all the brackets. = takes precedence over OR. So,

    if custid=123 or custid=9000 or custid=27000 then...

    is valid. You do need to specify each comparison though (which was not done in the original message).

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

    Re: how to write an array? (Access 2000)

    The easiest way is to use a Select...Case statement.

    Select Case CustID

    Case 123, 900,6300, 5000, 770
    ... put your message here
    Case Else
    ... just in case
    End Select
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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