Results 1 to 9 of 9
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Getting value from a value list in a query?

    Hi all,
    I am wondering if in a query I can get a value of a number as it refers to a value list?

    The qry would be like
    Code:
    TransType: [TransTypeID] = 1;"Correction";2;"Deposit";3;"Open Balance";4;"Payment";5;"Purchase";6;"Receive Funds";7;"Transfer Funds";8;"Withdrawal"
    TransTypeID is in query and is a number from my value list
    The value list is 1;"Correction";2;"Deposit";3;"Open Balance";4;"Payment";5;"Purchase";6;"Receive Funds";7;"Transfer Funds";8;"Withdrawal"

    Basically I am wondering if I can get Column(1) from the value list into a query somehow?
    Thanks
    Dave

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can use the Switch() function to do it but I would recommend creating a lookup table to store that then it becomes a simple matter of joining the new table to the query:
    https://support.microsoft.com/en-us/...3-f47504f9e379
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    join your data table to the List table on the key field, then bring down any fields needed.
    or
    in a forms listbox: sql: ...where
    [listCode] = forms!fMyForm!lstBox

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    value list on a combo box!

    I may have not explained correctly?

    In the relating table to my query, I have a field TransTypeID
    This is from a combo box in the form and is bound to 1 or Column 0
    It is a value list and the list is
    1;"Correction";2;"Deposit";3;"Open Balance";4;"Payment";5;"Purchase";6;"Receive Funds";7;"Transfer Funds";8;"Withdrawal"

    Is there a way within the query to get a value of TransTypeID Text? Example if TransTypeID = 3 then TransType: Would say Open Balance

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    perhaps a DLookup function in the query
    or
    use DLookup to set a tempvars variable and put the tempvar in the query before needing to use the query.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Dave, I understood where the value list came from but as I suggested it is usually better to use a lookup table instead. In any case have you looked at the Switch() function?

    TransType: Switch([TransTypeID] = 1,"Correction",[TransTypeID] = 2,"Deposit",[TransTypeID] = 3,"Open Balance",[TransTypeID] = 4;"Payment",[TransTypeID] = 5,"Purchase",[TransTypeID] = 6,"Receive Funds",[TransTypeID] = 7,"Transfer Funds",[TransTypeID] = 8,"Withdrawal")
    When time comes to add a new option or modify an existing entry you will be chasing the forms and queries to do the change when having a lookup table instead would involve one edit....

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much! I used a value list in my combo and set properties so you cant add any new! These should be the only ones ever needed I believe.
    Let me play with this and again, thank you
    Dave

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by d9pierce1 View Post
    Thank you so much! These should be the only ones ever needed I believe.

    Dave
    Famous last words
    Put a limit to the autonumber in the source, so that you only ever pull in those 8, even if a user manages to add 9, 10 etc.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thanks Welshgasman
    I will do that

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

Similar Threads

  1. Replies: 8
    Last Post: 12-13-2017, 10:38 AM
  2. Replies: 3
    Last Post: 07-12-2017, 12:03 PM
  3. Replies: 1
    Last Post: 02-15-2016, 04:48 PM
  4. Replies: 1
    Last Post: 04-21-2014, 08:00 AM
  5. Replies: 4
    Last Post: 06-16-2011, 09:30 PM

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