Results 1 to 14 of 14
  1. #1
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13

    creating a calculated column

    Hi everyone,

    i wish to create a new column on my access form such that;

    If value of column [Term]="01W" then column [Final tolerance] should = value of [Term]



    BUT

    If value of column [Term]="01M" then column [Final Tolerance] should= value of [Term]

    AND

    If value of column [Term]="02M" then column [Final Tolerance] should= value of [Term]

    Any help would be highly appreciated thanks

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Create a query first so as to get the calculated field

  3. #3
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    thanks for your reply. have actually got a query for it already with a new column, i just don't know how to put the expression together to give me what i want, could you help me with that? thanks

  4. #4
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    The logic seems
    [Final tolerance]=[Term] always!?

  5. #5
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    Hi everyone,
    I now have this query expression working for one column (Tolerance1WK)
    Final Tolerance: IIf([Term]="01W",[Tolerance1WK])

    but i don't know how to make the condition include the other two columns (Tolerance1M and Tolerance2M)

    Any help please????

  6. #6
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    thanks again, I now have this query expression working for one column (Tolerance1WK)
    Final Tolerance: IIf([Term]="01W",[Tolerance1WK])

    but i don't know how to make the condition include the other two columns (Tolerance1M and Tolerance2M)

    Any help please????

  7. #7
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Is it "OR" condition?

  8. #8
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Is it something like that:-
    Code:
    [Final Tolerance]: iif([Term]="01W",[Tolerance1WK], iif([Term]="01M", [Tolerance1M], iif([Term]="02M", [Tolerance2M], "")))

  9. #9
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    Perfect! perfect! i just copied your expression and it worked. i did not put the square bracket on "final tolerance" though. thanks for your time

    but then, is there any way i can make final tolerance show the conditional format of whatever column it get value from? without this been done, the whole essence of the expression is almost useless....FINAL HELP PLEASE. Thanks

  10. #10
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    columnName: "Tolerance" & [term]

    Final Tolerance: iif([Term]="01W",[Tolerance1W], iif([Term]="01M", [Tolerance1M], iif([Term]="02M", [Tolerance2M], "")))

  11. #11
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    thanks weekend00, already did that and its working, but then, is there any way i can make column "final tolerance" show the conditional format of whatever column it get value from? without this been done, the whole essence of the expression is almost useless....FINAL HELP PLEASE. Thanks

  12. #12
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    how do you want to show the conditional format? can you give an example?

  13. #13
    bold01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    13
    hi weekend00, based on the expression you gave earlier, do u think there is a way i could make the fields in column 'final tolerance' to automatically have the conditional format properties of the field its getting values from?

  14. #14
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    please give an example of how you want to show.

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

Similar Threads

  1. Replies: 7
    Last Post: 04-27-2010, 02:47 PM
  2. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  3. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM
  4. Replies: 0
    Last Post: 02-14-2007, 01:35 PM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

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 - Senior Forums