Results 1 to 6 of 6
  1. #1
    michalopala is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Posts
    1

    Help needed with calculated field.

    Hi All,

    Thank for stepping by. I am new to Access and I would really use your help. I am trying to develop management system. The problem I encountered is as follow:

    1. I have two fields in table ´Category´ has following possible values ´New´ or ´Renew´ and ´Priority´ has ´Low´, ´Medium´, ´High´.

    2. I need third field Capacity.

    3. The Capacity should be calculated based on Category and Priority i.e user selects New + Low then Capacity should be 1.



    I tried switch and if statement but I got constant errors. All help much appreciated. Please let me know in case any questions.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a conversion table, join it to your main query linked on CATAGORY. no code needed.
    or in a form, put it in a combo box, then when user select Priority, it fills Capacity (from column 2)

    Priority, Capacity
    New, 1
    Low, 1

    etc

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think ranmans suggestion is not quite right - table should be

    Category..Priority..Capacity
    New.........Low.........1
    New.........Medium....2
    Renew......Low.........3

    etc

    and join this in a query (or use a dlookup, or in a form use a dlookup or combo), don't use a calculated field in a table which is what you seem to be trying to do. calculated fields cannot be indexed so if you need to filter, apply criteria or sort on that field, performance will be slow once you start filling up with data.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I never use them either.
    See About calculated table fields
    Last edited by ssanfu; 05-13-2022 at 06:16 PM. Reason: Fixed the link

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Steve, the link isn't working for me. Yours is missing : from http:. Here it is again http://allenbrowne.com/casu-14.html

    If you use a table, either a compound link on Category and Priority fields or save an ID key and link on PK and FK or use DLookup().

    Otherwise, use VBA custom function and hard code calculation for Capacity. Cal function from query or textbox. What are the values for each Category/Priority pair?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @June7, Thanks. I edited the link. Seems to work now.......

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

Similar Threads

  1. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  2. Replies: 1
    Last Post: 07-01-2016, 08:24 AM
  3. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  4. Replies: 1
    Last Post: 11-30-2014, 12:31 PM
  5. Replies: 5
    Last Post: 09-14-2014, 02:34 PM

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
  •  
Other Forums: Microsoft Office Forums