Results 1 to 2 of 2
  1. #1
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92

    Field selection based on quey results

    Hello,



    I am looking for way to designate a field in a table to query based on the results of another query. Previously I was using the switch function but a change in the table's parameters has made the formula too large to enter.

    Basically the first query calculates each subscribers family status up to 5 children and assigns a numeric code (Family code). There are 23 codes. Each of the 23 codes are represented as fields on the table that houses the premiums. The table as a whole contains rates by Client ID, benefits and date spans. What I need is a way for query2 to look at the family code results from query1 and use that to search the rate table where the field name matches the family code. For example, if the query1 result is 200 then query2 would be linked to [Rates]![200], and if the result was 305 it would be linked to [Rates]![305] and so on. I hope that makes sense. Basically the table reference will be static but the field reference will be variable. Again, I previously had this working with the switch function so I know it is possible, I just cannot figure out how to code and I am assuming that the solution will need to be VBA but other options and advice are welcome.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe DLookup:

    DLookup([Family Code], "Rates", "ClientID=" & [ClientID])

    Additional criteria as needed to target the correct record.
    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.

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

Similar Threads

  1. Field visibility based on ComboBox selection
    By cactuspete13 in forum Forms
    Replies: 3
    Last Post: 01-30-2013, 03:37 PM
  2. Replies: 1
    Last Post: 10-30-2012, 10:29 AM
  3. Replies: 10
    Last Post: 11-21-2011, 02:56 AM
  4. Populate a field based on combobox selection
    By rscott7706 in forum Access
    Replies: 5
    Last Post: 06-02-2011, 03:18 PM
  5. Replies: 0
    Last Post: 04-17-2008, 09:24 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