Results 1 to 10 of 10
  1. #1
    blakej is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4

    If then statement on calculated field


    I am in the process of creating a form that needs to have several calculated fields. My biggest issue is that I need a VBC that will allow field "H" to autofill dependent on field "percentage of hours to complete." Essentially I need "H" to autofill with 100% if the %of hours to complete is over 60.1%, if it is not over 60.1% then I need it to fill in with the percentage from the hours to complete field.

    Thanks a million,

    Jen

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by blakej View Post
    I am in the process of creating a form that needs to have several calculated fields. My biggest issue is that I need a VBC that will allow field "H" to autofill dependent on field "percentage of hours to complete." Essentially I need "H" to autofill with 100% if the %of hours to complete is over 60.1%, if it is not over 60.1% then I need it to fill in with the percentage from the hours to complete field.

    Thanks a million,

    Jen
    Are the calculated fields just for display or do you need to store those values?

    You can open the forms recordset, add a new field in the query (not adding a table field, a query field) make a calculation based off the other fields and have that ready to use on the form. Then back on the form you can create a textbox control that has the control source of that calculated query field you just made to display on your form.

    Or you could use VBA

    with VBA you could code something to fill a table field on a record after something has been entered. So you can have an "after update" event when you have entered some details the code will execute after entering it and it will field the field with the calculation you have made in the VBA.

    e.g.

    textbox1_afterupdate()

    me.txtbox1 = 3 + 4 'txtbox1 is the name of the control on the form, it becomes the calculation
    me.refresh 'refresh the form
    end sub

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    basic example of a simple iif statement

    for your calculation I would use iif(h>=60, "Complete","uncomplete")

  4. #4
    blakej is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    i placed this code into the VB after update and I am getting a Compile error: Expecte:=???

  5. #5
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by blakej View Post
    i placed this code into the VB after update and I am getting a Compile error: Expecte:=???
    You can't simply copy and paste what I have written - that is simply an example

  6. #6
    blakej is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    No I did not I created the code based on your example, but i am receiveing that error.

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I will break it down for you

    iif() <- this is the method

    h <this is your field, you could h or FirstName or whatever - it's the column in your table.

    >= <operator, this means greater than or equal to

    60 <this can be another field or a number or a percent etc etc etc, whatever you want to greater or less than

    then you add a ,

    the first , is the true result

    the second, is the false
    e.g.
    iif(calc, true, false)


    iff(1>=3, " greater than 3", "less than 3") -----> this will give you "less than 3"

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by blakej View Post
    No I did not I created the code based on your example, but i am receiveing that error.
    copy paste your code and post here

  9. #9
    blakej is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    Thanks for the breakdown since im clearly incopetent; but that is what I tried and it is not working. I think I will just try the first alternative you suggested and if not I will simply have to research and possibly take a class on the subject.

  10. #10
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by blakej View Post
    Thanks for the breakdown since im clearly incopetent; but that is what I tried and it is not working. I think I will just try the first alternative you suggested and if not I will simply have to research and possibly take a class on the subject.
    yeah I'm happy to explain anything - it's hard enough to explain something or convey something to an expert user (not me, June7, Itsme, others on this forum - who all give fantastic help) so feel free to ask

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  2. Replies: 3
    Last Post: 05-06-2013, 03:03 PM
  3. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  4. Replies: 3
    Last Post: 02-13-2013, 10:15 AM
  5. Calculated field help
    By kek979 in forum Queries
    Replies: 1
    Last Post: 09-28-2012, 10:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums