Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    31

    Unanswered: IFF statment required here?

    Hi

    On my form I have two boxes. The first box is called BATCH QTY. This is where I enter a number of parts produced.

    The second box on the form is called REQUIRED SAMPLE SIZE. I would like this box to tell me (based on the number entered in the first box called BATCH QTY) If the “BATCH QTY IS TOO SMALL” or is “50” or is “80” or is “125” or if “BATCH SIZE IS TOO HIGH”.

    This is the working code that I use in MS Excel:
    =IF(F3<1200,"batch is too small for
    AQL",IF(AND(F3>=1201,F3<=3200),50,IF(AND(F3>=3201, F3<=10000),80,IF(AND(F3>=10001,F3<=35000),125,IF(A ND(F3>=35001,F3<=150000),200,"batch size too high")))))[/SIZE]

    Can anyone convert this into a statement MS Access can understand. I was trying to put this in the control source of the box called “REQUIRED SAMPLE SIZE” using the expression builder but to no avail!

    Any help for the novice would be much appreciated.

    Many thanks
    MGF23

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You should not use spaces or any other special characters when naming the objects (forms, tables, controls, etc.) of your database and you should not use any reserved words (Date, Value, etc.). Sooner or later this will cause problems. See:
    General: Commonly used naming conventions
    Microsoft Access tips: Problem names and reserved words in Access
    Avoid space characters in any 'Name' - Why ? - Microsoft: Access Tables and Relationships FAQ - Tek-Tips

    2. You could use this:
    Code:
    Private Sub BATCH_QTY_AfterUpdate()
    
        Select Case Me.BATCH_QTY.Value
            Case Is < 1201:         Me.REQUIRED_SAMPLE_SIZE.Value = "batch is too small for AQL"
            Case 1201 To 3200:      Me.REQUIRED_SAMPLE_SIZE.Value = 50
            Case 3201 To 10000:     Me.REQUIRED_SAMPLE_SIZE.Value = 80
            Case 10001 To 35000:    Me.REQUIRED_SAMPLE_SIZE.Value = 125
            Case 35001 To 150000:   Me.REQUIRED_SAMPLE_SIZE.Value = 200
            Case Is > 150000:       Me.REQUIRED_SAMPLE_SIZE.Value = "batch size too high"
        End Select
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would not recommend mixing text and numeric values
    I guess it depends what happens to the values in that control (eg if you use 50,80.... etc and store them then you now have a problem in as much as a column that should be defined as numeric isn't

    what you probably should do is store NULL where the batch size incorrect, and dispaly a text message elsewhere

    whether thats using a message box to alert the user of the problem,

    eg

    msgbox ("Oi slaphead, your batch quantity ois out of range" & vbcrlf & "fix it or else", vbwarning)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2012
    Posts
    31
    Thanks for you knowledge and expertise on this guys it a great help ;-)

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Apr 2012
    Posts
    31
    Quick update guys. Form is now working a treat. Thanks. I now have a message box that when the conditions are not met it warns the 'slap heads' to get it right ;-)

Tags for this Thread

Posting Permissions

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