Results 1 to 9 of 9
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    display case select in query

    It's time to ask the experts for help. I have a table with EHT_Stage variable filled in and based on the value in this field it supposed to grab the initials from a different column in the table and display it in query.
    For example
    if EHT_Stage has value of 6 then go to column "EHT_Designed_By" and show designer initials stored in this field
    if EHT_Stage has value of 0 then display "N/A"

    I came up with function but what it does it loops trough record sets and shows only "N/A" and ignores the records when I am expecting to see initials. Below is output from my query, vba code and sql Qry.
    Thanks for your help


    EHT_By P_Iso_Dwg EHT_Stage
    N/A 47-9001-01 4

    47-9001-02 5
    N/A 47-9001-03 1

    47-9001-04 6

    Code:
    Option Compare Database
    
    Option Explicit
    Public Function funcEhtBy(EHT_Stage As String)
    Dim EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By As Variant
    
    
    Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        
        'set default return value
         funcEhtBy = "0"
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, " & _
        "EHT_Back_Checked_By  from tbl_Tracking"
    
    
    Set RsEHT_By = CurrentDb.OpenRecordSet(strEHT_By)
        With RsEHT_By
        If Not .BOF And Not .EOF Then
          .MoveLast
            .MoveFirst
    While (Not .EOF)
    
    
    Select Case EHT_Stage
    
    
    Case "0", "1", "2", "3", "4", "16", "17"
    funcEhtBy = "N/A"
    Case "5"
    
    
    funcEhtBy = EHT_Re_Designed_By
    
    
    Case "6"
    funcEhtBy = EHT_Designed_By
    
    
    Case "7"
    funcEhtBy = EHT_Designed_By
    
    
    Case "8"
    funcEhtBy = EHT_Drafted_By
    
    
    Case "9"
    funcEhtBy = EHT_Extracted_By
    
    
    Case "10"
    funcEhtBy = EHT_Modeled_By
    
    
    Case "11"
    funcEhtBy = EHT_Drafted_By
    
    
    Case "12"
    funcEhtBy = EHT_Checked_By
    
    
    Case "13"
    funcEhtBy = EHT_Checked_By
    
    
    Case "14"
    funcEhtBy = EHT_Back_Drafted_By
    
    
    Case "15"
    funcEhtBy = EHT_Back_Checked_By
    
    
    Case Else
    funcEhtBy = ""
    
    
      End Select
      .MoveNext
      Wend
     End If
    End With
        RsEHT_By.Close
        Set RsEHT_By = Nothing
    End Function
    sql query
    Code:
    SELECT funcEhtBy([EHT_Stage]) AS EHT_By, tbl_Tracking.P_Iso_Dwg, tbl_Tracking.EHT_StageFROM tbl_Tracking;

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure I grasp the issue. You are trying to use this function in a query to populate a query field?
    Your function is not set to return anything so I don't see how it can work - admit that I've never tried to return anything from a function without setting a return type as in
    Public Function funcEhtBy(EHT_Stage As String) As String

    stage looks like a number but you're treating it as text. Is that correct, because you set the value to N/A early on (which you say it always returns)? So maybe it is never anything else? Anyway, my biggest "problem" is that the function has no return data type. I know that you have funcEhtBy = EHT_Back_Checked_By ; I've just never tried your method, nor would I. It's just not how I expect it to work.
    Did you step through this code and validate the variables?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    Smile

    Micron, Thanks for the reply, Yes I am trying to populate query field using a function. EHT_Stage is a text string.
    What I found out when playing with this code is that code evaluates "EHT_Stage" variable but it doesn't bring me the values from the columns where initials reside.
    For example when I changed column names in case function to string it gives me the column name as expected, so the way I see it is that the code doesn't bring a record set to the function and I have problem correcting it.
    EHT_By P_Iso_Dwg EHT_Stage
    N/A 47-9001-01 4
    EHT_Re_Designed_By 47-9001-02 5
    N/A 47-9001-03 1
    EHT_Designed_By 47-9001-04 6

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Dim EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By As Variant
    'The above variables are never given a value, so can be omitted.
    
    
    Case "0", "1", "2", "3", "4", "16", "17"
    funcEhtBy = "N/A"
    Case "5"
    
    funcEhtBy = !EHT_Re_Designed_By
    
    'Without the bang, it refers to the dimmed variable noted above, not the fieldname in the recordset.

  5. #5
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    davegri, I have corrected code as per your sggstios, but stil have te same problem, the code doesn't bring me the initials to qry. Since the fields with initials can contain Null values I have defauled Null values to "-"
    this is what I am getting
    EHT_By P_Iso_Dwg EHT_Stage
    - 23-9001-05 7
    - 23-9002-01 6
    - 23-9002-03 5
    N/A 23-9002-04 4
    - 23-9002-05 8

    I chcked the table and there are initials for columns with stages 5,6,7,8 and I can't figure it out why the code doesn't give me the right output. Wonder if there is a easir way to do that?

    Quote Originally Posted by davegri View Post
    Code:
    Dim EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By As Variant
    'The above variables are never given a value, so can be omitted.
    
    
    Case "0", "1", "2", "3", "4", "16", "17"
    funcEhtBy = "N/A"
    Case "5"
    
    funcEhtBy = !EHT_Re_Designed_By
    
    'Without the bang, it refers to the dimmed variable noted above, not the fieldname in the recordset.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Did you add the bang to the case 5,6,7,8 elements also?

    Also, as Micron indicated, you need a return data type for the function:

    Public Function funcEhtBy(EHT_Stage As String) as String

    If this doesn't help, we need to see your db for analysis.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Deleted, duplicate of #6

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Code:
    'set default return value
         funcEhtBy = "0"
    The above is probably overwritten by the case statements so is probably not useful.

    you should probably deal with nulls with the Nz() function


    Code:
    funcEhtBy =Nz(!EHT_Re_Designed_By,"0")

  9. #9
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    After incorporating micron, davegri and moke123 comments I was preparing dBase to post it on the forum as davergi suggested. I run the query one more time and to my surprise it worked as it should. What I found out that some records in EHT_Stage I built my function on contained Null values
    Thank you very much for your help.
    here is the final code
    Code:
    Public Function funcEhtBy(EHT_Stage As String) As String
    
    Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, " & _
        "EHT_Back_Checked_By  from tbl_Tracking"
    'Debug.Print strEHT_By
    Set RsEHT_By = CurrentDb.OpenRecordSet(strEHT_By)
        With RsEHT_By
        If Not .BOF And Not .EOF Then
          .MoveLast
            .MoveFirst
    While (Not .EOF)
    
    
    Select Case EHT_Stage
    
    
    Case "0", "1", "2", "3", "4", "16", "17"
    funcEhtBy = "N/A"
    Case "5"
    funcEhtBy = Nz(!EHT_Re_Designed_By, "0")
    
    
    Case "6"
    funcEhtBy = Nz(!EHT_Designed_By, "0")
    
    
    Case "7"
    funcEhtBy = Nz(!EHT_Designed_By, "0")
    
    
    Case "8"
    funcEhtBy = Nz(!EHT_Drafted_By, "0")
    
    
    Case "9"
    funcEhtBy = Nz(!EHT_Extracted_By, "0")
    
    
    Case "10"
    funcEhtBy = Nz(!EHT_Modeled_By, "0")
    
    
    Case "11"
    funcEhtBy = Nz(!EHT_Drafted_By, "0")
    
    
    Case "12"
    funcEhtBy = Nz(!EHT_Checked_By, "0")
    
    
    Case "13"
    funcEhtBy = Nz(!EHT_Checked_By, "0")
    
    
    Case "14"
    funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
    
    
    Case "15"
    funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
    
    
    Case Else
    funcEhtBy = ""
    
    
      End Select
      .MoveNext
      Wend
     End If
    End With
        RsEHT_By.Close
        Set RsEHT_By = Nothing
        
    End Function
    Quote Originally Posted by moke123 View Post
    Code:
    'set default return value
         funcEhtBy = "0"
    The above is probably overwritten by the case statements so is probably not useful.

    you should probably deal with nulls with the Nz() function


    Code:
    funcEhtBy =Nz(!EHT_Re_Designed_By,"0")

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

Similar Threads

  1. Pl sql case select
    By mrmmickle1 in forum Queries
    Replies: 1
    Last Post: 11-17-2015, 11:14 PM
  2. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM
  3. select case problem
    By Mclaren in forum Programming
    Replies: 3
    Last Post: 11-17-2011, 01:28 PM
  4. select case or else if for unhiding
    By nichmeg in forum Programming
    Replies: 3
    Last Post: 10-30-2011, 09:30 AM
  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