Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: "If,Then,ElseIf.." or "Select Case"

    Hi experts,

    I am using Access2002.
    In a table named as "IPPtable" populated with data, I have 3 among other Fields and they are named as:


    Now I want to create a 4th Field named [TSLcheck] base on the following required criteria in its order of priority from 1) to 6), to use either "If,Then,ElseIf.." or "Select Case" procedures.

    1) If [MINqty]=[TSLqty], then in [TSLcheck] Debug.Print "no action needed"
    2) If [MINqty]=1, [MAXqty]>1 and [TSLqty]>[MINqty]<MAXqty] Debug.Print "no action needed"
    3) If [MINqty]=0 and [TSLqty]>0, then in [TSLcheck] Debug.Print "to remove TSL?"
    4) If [MINqty]>0 and [TSLqty]=0, then in [TSLcheck]
    Debug.Print "to set TSL?"
    5) If [TSLqty]>0 and [MINqty]>[TSLqty], then in [TSLcheck] Debug.Print "to increase TSL?"
    6) If [TSLqty]>0 and [MINqty]<[TSLqty], then in [TSLcheck] Debug.Print "to decrease TSL?"

    [MINqty] [MAXqty] [TSLqty] [TSLcheck]
    1 1 1 no action needed
    1 3 2 no action needed
    0 0 1 to remove TSL?
    1 1 0 to set TSL?
    2 2 1 to increase TSL?
    1 1 2 to decrease TSL?

    Note that criteria using [MAXqty] only apply to item 2).

    Any help is very much appreciated !
    Attached Files Attached Files
    Last edited by alienscript; 10-16-04 at 02:39. Reason: inappropriate Fieldnames with spaces

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    having a go...

    iif((([MINqty]=[TSLqty]) OR (([MINqty]=1 AND [MAXqty]>1) AND ([MINqty] BETWEEN [TSLqty] AND [MAXqty]))), “nothing”, iif([TSLqty]>0, iif([MINqty]=0, “remove”,iif([MINqty]>[TSLqty],“increase”, “decrease”))))

    what a mess!

    Last edited by izyrider; 10-16-04 at 12:20.
    currently using SS 2008R2

Posting Permissions

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