Results 1 to 2 of 2
  1. #1
    critusodem is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4

    Use Table Field Values for the output from Select Case Statements

    The first item is the Select Case.


    The second item is the table that is currently not being used by Select Case.

    What I would greatly appreciate is learning how to use the table for what the Select Case reads to define the Case output.

    The Select Case will use tblIncomeguidelines in Case 1 instead of using "fSort = "41000"," it pulls the [Annual] value from [Household] 1.
    The Select Case will use tblIncomeguidelines in Case 2 instead of using "fSort = "46950"," it pulls the [Annual] value from [Household] 2.
    The Select Case will use tblIncomeguidelines in Case 2 instead of using "fSort = "52800"," it pulls the [Annual] value from [Household] 3.
    etc...

    Thank you for your time and effort,
    -Chris


    Code:
    Option Compare Database
    
    Option Explicit
    
    Public Function fSort(Product As String)
    
    Select Case Product
    
         Case "1"
         fSort = "41000"
    
         Case "2"
         fSort = "46950"
         
         Case "3"
         fSort = "52800"
         
         Case "4"
         fSort = "58650"
         
         Case "5"
         fSort = "63350"
         
         Case "6"
         fSort = "68050"
         
         Case "7"
         fSort = "72750"
         
         Case "8"
         fSort = "77450"
         
         Case "9"
         fSort = "82110"
         
         Case "10"
         fSort = "86802"
         
         Case "11"
         fSort = "91494"
         
         Case "12"
         fSort = "96186"
         
         Case "13"
         fSort = "100878"
         
         Case "14"
         fSort = "105570"
         
         Case "15"
         fSort = "110262"
         
         Case "16"
         fSort = "114954"
    
    
         Case Else
         fSort = "Check Value"
    
    End Select
    
    
    End Function
    Table: tblIncomeguidelines
    Code:
    Household Annual
    1 $41,000.00
    2 $46,950.00
    3 $52,800.00
    4 $58,650.00
    5 $63,350.00
    6 $68,050.00
    7 $72,750.00
    8 $77,450.00
    9 $82,110.00
    10 $86,802.00
    11 $91,494.00
    12 $96,186.00
    13 $100,878.00
    14 $105,570.00
    15 $110,262.00
    16 $114,954.00

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You wouldn't use Select/Case, you'd use DLookup() or open a recordset using the input in the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 03-06-2015, 11:16 AM
  2. Many many if/then statements or case?
    By breakingme10 in forum Programming
    Replies: 13
    Last Post: 07-11-2014, 09:11 AM
  3. Debugging a Select Case Statements
    By dccjr in forum Access
    Replies: 4
    Last Post: 03-05-2013, 04:14 PM
  4. Debugging a Select Case Statements (Still)
    By dccjr in forum Programming
    Replies: 13
    Last Post: 02-28-2013, 09:47 PM
  5. Select Case using query field
    By focosi in forum Programming
    Replies: 4
    Last Post: 08-15-2011, 11:00 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