Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Function to return selected field name

    Hi,
    I've a query contains many fields & calculated fields, I used many iifs to check it a specific field value is equal to a field name to return the record value.
    The iifs are limited to 13 iffs and when I added the 14th one I got an error message that the expression is too complicated, so I decided to change it to a function with case statements.
    I created such function to check if the specific field value is equal to a field name to return the record value from that field.
    I called this function on the query but unfortunately the returned value to the query was the called name (e.g. [B]) not the value of the field [B]
    Example:
    Code:
    Function SetMultiplier(Multiplier As String) As String
    Select Case Multiplier
        Case "A"
            SetMultiplier = "[A]"
        Case "B"
            SetMultiplier = "[B]"
        Case "C"
            SetMultiplier = "[C]"
        Case Else
        0
     End Select
    End Function
    Where A, B & C are fields name


    I tried to change single & double quotation & many but failed.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That function is not going to be able to pull values from fields [A], [B], [C] unless you call the function from a textbox or open a recordset in the function or maybe DLookup().

    Switch() can often be used in lieu of nested IIf().

    Switch([Multiplier]="A", [A], [Multiplier]="B", [B], [Multiplier]="C", [C])

    If [Multiplier] held a number value, Choose() function could possibly be appropriate.
    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.

  3. #3
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks June7.
    I tried both;
    Switch() is limited also to 14 expressions & values & I got same iifs error on the 15th one (The expression you entered is too complex)
    I tried Choose() in addition to a function to return a numeric value for the FieldName (e.g. Case "A" --> 1 , Case "B" -->2) but it slow down the query (almost dead)
    I'll Try Choose() again without using the function and see.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, Choose() only works if the input is a number value. Google it.

    I just tested Switch() with 16 elements and it works. Post your attempted expression. It's possible it is just too many characters.

    A DLookup() expression in query can possibly accomplish.

    Test: DLookup("[" & [Multiplier] & "]", "tablename", "ID=" & [ID])

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This sounds like you are storing values in your field names, which you shouldn't do.
    Even if you are not, if you have 13 data points then you should consider moving this data and the multiplier to a separate table, which you can then join on.

    When you add the 14th or 30th you simply add them to the table and everything still works.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Function SetMultiplier(Multiplier As String) As StringSelect Case Multiplier
        Case "A"
            SetMultiplier = Me.[A]
        Case "B"
            SetMultiplier = Me.[B]
        Case "C"
            SetMultiplier = Me.[C]
        Case Else
     End Select End Function
    If [A], [B] and [C] are on the same form as the function, try above to return the field values.

  7. #7
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    June7
    Yes, choose() is working with numbers but takes long time to run the query & the query is almost dead while scrolling, also the report based on that query takes more than 5 minutes then I forced it to close.
    By using switch(), it worked when I reduced an element, but slower than iifs, the query is complicated.
    I'll check Dlookup.
    Thanks for your advice

  8. #8
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Minty,
    My project is an engineering project, it is so complected & I'm using a concept of multiplier to select a specific field to multiply its value into other.
    As an example if I've 100s of items (ID, Name, Size, Quantity, Unit, ClassFactor, MaterialFactor, weightFactor,.....), the required field calculated by multiplying the quantity by one of these fields based on a condition. I named a filed "Multiplier" as a short text contains the field name(e.g. ClassFactor, MaterialFactor, ...) & on my required filed I used iif [Multiplier] = [ClassFactor], [Qty]*[ClassFactor], iif [Multiplier] = [MaterialFactor], [Qty]*[MaterialFactor],iif.....
    That is in brief.
    I din't understand how moving multiplier to a separate table will help.
    Thanks for responding.

  9. #9
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thank Davegri, but I'm using the function inside the query not in a form.

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It does sound as if your data isn't normalised properly.
    If an item could have one or more possible Type of Factors, but there are many and varied factors then you should record the factor type as a child record of the parent item.
    If it can only have one factor then you should store the factore type not have a field for each possible one.

    Then you only need to multiply by that one field.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by ashraf View Post
    Thank Davegri, but I'm using the function inside the query not in a form.
    In that case how about:
    Code:
    Function SetMultiplier(Multiplier As String) As String 
     Select Case Multiplier
        Case "A"
            SetMultiplier = Forms!myForm.[A]
        Case "B"
            SetMultiplier = Forms!myForm.[B]
        Case "C"
            SetMultiplier = Forms!myForm.[C]
        Case Else
     End Select 
    End Function
    Last edited by davegri; 01-23-2019 at 11:30 AM. Reason: layout

  12. #12
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks Minty,
    Actually I can't store the variable field data without calculating it, because it varies based on many conditions as I explained; if the item(record) is a pipe then I'll use the field "X1" as a multiplier & if the item is a Steal Structure I will use field "X2" & so on. X1,X2,.... are stored with the item type on other table

  13. #13
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Davergi,
    I have no form linked to such query as I explained.

  14. #14
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you missed my point, in that how you store that information completely changes how you would try and perform the calculation.

    If you post up your table design(s) and how they are related I think it would be easier to explain. And also see your problem more clearly.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi all!

    ashraf,

    You can use this way of expression:
    Code:
    SelFactor: (([Multiplier] = "ClassFactor")*[ClassFactor])+(([Multiplier] = "MaterialFactor")*[MaterialFactor])+(([...
    and finally:

    Code:
    Result: [Qty]*[SelFactor]
    If it exceed in characters the limit of expression builder (I think that is up to 1024), you can split it in separately fields and finally add them in a total field.
    For example:
    Code:
    Result: [Qty]*([Factor1]+[Factor2]+[Factor3])

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