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

    Unanswered: Using an excel formula in Access

    High guys/gals

    I have a spreadsheet that I am moving into a database. Currently in Excel the cell F2 is where i enter the quantity of 'WIDGETS' Produced and in another cell the result of the below statement is produced:

    =IF(F2<1200,"batch is too small for AQL",IF(AND(F2>=1201,F2<=3200),50,IF(AND(F2>=3201, F2<=10000),80,IF(AND(F2>=10001,F2<=35000),125,IF(A ND(F2>=35001,F2<=150000),200,"batch size too high")))))

    My question is can I use this in a form. I want to enter the number of WIDGETS in one text box and in a second box display the result of the above IF statment.

    A solution to my little problem will be greatly appreciated

    Many thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so effectively what you have is a validation rule
    place some code in the relevant control(s) probably based on when that/those controls loose their focus

    if (mycontrolF2.value <1200) then
    msgbox "Proposed batch size is too small," & vbcrlf & "Must be at least 1200", vbwarning
    elseif (mycontrolF2.value >=1200 and mycontrolF2.value <=3200) then
    'do somehting
    elseif (mycontrolF2.value >=1200 and mycontrolF2.value <=3200) then
    'do somehting
    else
    msgbox "Proposed batch size is too big," & vbcrlf & "Must be no more than 150,000", vbwarning
    endif

    as to where the code goes, thats up to you and how you want this handling

    I'd probably place it inside a function and call that function from your control that contains the equivalent of F2 or as I called it mycontrolF2.

    you probably want to run this everytime something changes. whether you do that as part of the form (or controls) "before update event". if so setting cancel = true stops the data being updated
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    Posts
    31

    Just to clarify

    Thanks for your ultra quick reply, but just to clarify:

    My second text box is where i want the result of the validation rule to appear, based on the input of my first box. So if i double click on the property of the second box and copy this code in somewhere - replacing "mycontrolF2" with the name of the first text box?

    Sorry - must point out i am a bit of an access novice

    Thanks you very much

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    if you want to do this befiore the record is saved then consider palcing the call in the relevant controls on lost focus event AND before update

    lets assume the control that holds the value of F2 is called F2, lets assume the destination control is called X23

    if F2.value <1200 then
    X23.text = 'something'
    elseif F2.Value <=2500
    X23.value = 50
    elseif F2.value >150000 then
    X23.text = 'something else'
    else
    X23.text = 'illegal value detected'
    endif
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2012
    Posts
    31
    Hi Heldem,

    I am sure the code/information is good - I am just having trouble interpreting it :-(

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
  •