Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27

Function to return selected field name

  1. #16
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    Hi accesstos,


    What is this function? it is like : if ([Multiplier] = "ClassFactor") is true then -1 else 0
    I tested it for 2 elements & is working fine but I've to multiply by (-1) because the results are in negative.
    I'll complete the equation & see.
    Thank you

  2. #17
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    Thanks Minty, I'll try to upload a sample of my database

  3. #18
    accesstos's Avatar
    accesstos is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    67
    Quote Originally Posted by ashraf View Post
    Hi accesstos,
    What is this function? it is like : if ([Multiplier] = "ClassFactor") is true then -1 else 0
    I tested it for 2 elements & is working fine but I've to multiply by (-1) because the results are in negative.
    I'll complete the equation & see.
    Thank you
    That's right ashraf! This expression (not function) multiplies the logical condition ([Multiplier] = "Class Factor"), which result can be 0 or -1, with the value of the corresponding field ([Class Factor]). Only one of all conditions can be TRUE. And yes, I just failed to state that the TRUE condition equivalent to -1.
    Therefor, the finally expression can be:
    Code:
    Result: [Qty]*-[SelFactor]
    Or
    Code:
    Result: [Qty]*ABS([SelFactor])
    Good continuing!
    John

  4. #19
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    Thanks accesstos,
    It's working but slow, with iif takes 3 seconds to move from 1st column to the last one (by using "End" Keyboard button) & with this expression it takes about 12 seconds.
    I think iif will not calculate the rest on expression if True but this expression must check all condition on each record.

  5. #20
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    Minty
    Below image is a sample of the DB structure it may explain my design.
    Attachment 37132
    Attached Thumbnails Attached Thumbnails Capture.jpg  

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,496
    Interesting picture, but can you provide a copy of your database with a few records so we can actually see the implementation.
    It would also be helpful if you provided some instructions to highlight any issues (eg speed etc).
    Last edited by orange; 01-26-2019 at 08:13 AM. Reason: spelling

  7. #22
    accesstos's Avatar
    accesstos is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    67
    ashraf,

    You may split the nested IIFs in two fields and add them in a third, but, obviously, this is a spreadsheet-way approach.
    In my opinion, you have to transpose the columns of factors to rows (one factor per record). Perhaps that increase the rows of the datasheet but, in Access, that would be a much more efficient and normalised data structure.

    Regards,
    john

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    You want to multiply by FactorX yet FactorX is a calculated value - how are the factor values derived?

    This is making even less sense after providing image.

    It appears each factor can associate with multiple GroupMultiplier but each GroupMultiplier has only 1 factor. Why isn't there a field in GroupsMasterList for Factor?
    Last edited by June7; 01-26-2019 at 04:05 PM.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #24
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    June7,
    Factors are expressions depend on the values of Item(record) specifications like material, Unit, .. etc
    e.g. if the unit is in meter(mt) and the material is SS the factor03 will be the weight per linear meter of the Stainless Steal (SS) item (value from Density table) and "actual quantity" will be the Qty*Factor03. if the unit is in DI(Dia Inch for pipes) then Factor04 will be the pipe weight per linear meter from pipe standard table.
    So Factor value is depend on the item specification and I can't use it as a Group(multiplier)

  10. #25
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    OK Orange, I'll try to put it in a sample database in access

  11. #26
    ashraf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    22
    John,
    As I explained to June7 about the factors, it depends on the item specifications and I can't transpose it to item rows because groups(multiplier) also contains sub items but to simplify the example I din't mention.
    Thank you.

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    So the factor used for each GroupMultiplier depends on specification inputs material and unit? Sounds like need a VBA custom function.

    Not following why you need 10 Factor fields calculated. Input parameters to a function and spit back the appropriate factor.

    Now I don't understand the relevance of Group identifier.

    And now you mention a 'pipe standard table'.

    Again, if you want to provide db for analysis, follow instructions at bottom of my post.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find Function and Return Value
    By Whughes98 in forum Queries
    Replies: 5
    Last Post: 10-09-2018, 03:17 PM
  2. Replies: 9
    Last Post: 08-19-2014, 11:41 AM
  3. VBA Return to the selected record
    By devcon in forum Forms
    Replies: 7
    Last Post: 02-17-2012, 08:17 AM
  4. Function to return a set of records?
    By vicrauch in forum Access
    Replies: 2
    Last Post: 07-12-2011, 07:27 AM
  5. Replies: 1
    Last Post: 06-23-2010, 08:45 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
  •  
Tech Forums: Microsoft Office Forums