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

    Select case function problem

    I have a code which suppose to evaluate value in "eht_stage" column and based on it grab initials from different columns and display it on form.


    For example if "eht_stage" = 3 then grab initials from column "eht_zoned". what I am getting is empty value although I know the initials are there.
    Can't figure it out what's the problem is, I am not getting any errors, Case 0,1,2 works as it should.


    HTML Code:
    Option Compare Database
    Option ExplicitDim db As DAO.database
    'get initialsPublic Function funcEhtBy(EHT_Stage As Integer) As StringDim strEHT_By As String    Dim RsEHT_By As DAO.Recordset        'get initials from table    strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _    "EHT_Reviewed_By, EHT_IFC_By  from tbl_Tracking"
    Set RsEHT_By = CurrentDb.OpenRecordSet(strEHT_By)    With RsEHT_By    If Not .BOF And Not .EOF Then      .MoveLast        .MoveFirstWhile (Not .EOF) Select Case Nz(EHT_Stage, "")
    Case 0, 1, 2funcEhtBy = "NotReq"
    Case 3funcEhtBy = Nz(!EHT_Zoned_By, "0")
    Case 4, 5funcEhtBy = Nz(!EHT_Designed_By, "0")
    Case 6, 9funcEhtBy = Nz(!EHT_Drafted_By, "0")
    Case 7funcEhtBy = Nz(!EHT_Extracted_By, "0")
    Case 8funcEhtBy = Nz(!EHT_Modeled_By, "0")
    Case 10, 11funcEhtBy = Nz(!EHT_Checked_By, "0")
    Case 12funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
    Case 13funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
    Case 14funcEhtBy = Nz(!EHT_Reviewed_By, "0")
    Case 15funcEhtBy = Nz(!EHT_IFC_By, "0")Case ElsefuncEhtBy = "TestEmpty"
      End Select  .MoveNext  Wend End IfEnd With    RsEHT_By.Close    Set RsEHT_By = Nothing    End Function

  2. #2
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    sorry, I wrapped the code incorrectly

    Code:
    Option Compare Database
    
    Option Explicit
    Dim db As DAO.database
    
    
    'get initials
    Public Function funcEhtBy(EHT_Stage As Integer) As String
    Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _
        "EHT_Reviewed_By, EHT_IFC_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 Nz(EHT_Stage, "")
    
    
    Case 0, 1, 2
    funcEhtBy = "NotReq"
    
    
    Case 3
    funcEhtBy = Nz(!EHT_Zoned_By, "0")
    
    
    Case 4, 5
    funcEhtBy = Nz(!EHT_Designed_By, "0")
    
    
    Case 6, 9
    funcEhtBy = Nz(!EHT_Drafted_By, "0")
    
    
    Case 7
    funcEhtBy = Nz(!EHT_Extracted_By, "0")
    
    
    Case 8
    funcEhtBy = Nz(!EHT_Modeled_By, "0")
    
    
    Case 10, 11
    funcEhtBy = Nz(!EHT_Checked_By, "0")
    
    
    Case 12
    funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
    
    
    Case 13
    funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
    
    
    Case 14
    funcEhtBy = Nz(!EHT_Reviewed_By, "0")
    
    
    Case 15
    funcEhtBy = Nz(!EHT_IFC_By, "0")
    Case Else
    funcEhtBy = "TestEmpty"
    
    
      End Select
      .MoveNext
      Wend
     End If
    End With
        RsEHT_By.Close
        Set RsEHT_By = Nothing
        
    End Function

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're getting an "empty value" only on case 3? Odd, because if the field is null, you're converting it to 0. Suggest you debug.print the function returned value for cases that don't seem to work, as in

    funcEhtBy = Nz(!EHT_Zoned_By, "0")
    debug.print "FuncEhtBy = " & functEhtBy & "."
    If you get nothing in between those two string parts, the field must be an empty string - or else the case is never 3?
    Last edited by Micron; 10-25-2019 at 10:23 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    With the While...Wend loop, the function returns always the value of the last record of RsEHT_By recordset. Is this that you really want?

    Give a try to the simpler version of funcEhtBy() function as seems bellow:
    Code:
    Public Function funcEhtBy(EHT_Stage As Integer) As String
        Dim arrFields As Variant
    
        arrFields = Split("EHT_Zoned_By, EHT_Designed_By, EHT_Designed_By, EHT_Drafted_By, " _
                          & "EHT_Extracted_By, EHT_Modeled_By, EHT_Drafted_By, EHT_Checked_By, EHT_Checked_By, " _
                          & "EHT_Back_Drafted_By, EHT_Back_Checked_By, EHT_Reviewed_By, EHT_IFC_By", ", ")
        If EHT_Stage > 2 Then
            If EHT_Stage < 16 Then
                'Debug.Print "Stage(" & EHT_Stage & "): " & arrFields(EHT_Stage - 3)
                funcEhtBy = Nz(DLookup(arrFields(EHT_Stage - 3), "tbl_Tracking"), "0")
            Else
                funcEhtBy = "TestEmpty"
            End If
        Else
            funcEhtBy = "NotReq"
        End If
    End Function
    Maybe that works as you expected.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    With the While...Wend loop, the function returns always the value of the last record of RsEHT_By recordset.
    I often tend to concentrate on the problem rather than examine all the code (especially when I find it hard to read due to lack of indentation or use of code tags) and figured I missed what you're pointing out. After reviewing I still can't see that. There is a Move Next before the end of the loop?

    What I do find odd is that the Update method of the recordset isn't invoked, so I don't see how this would work at all without an error.

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    After reviewing I still can't see that.
    I think Accesstos has it right. The value returned by the function is set by every record in the recordset, but the function is not exited until the last record, so all the previous records don't matter.
    Last edited by davegri; 10-26-2019 at 12:37 PM. Reason: sp

  7. #7
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Micron, I am not getting any errors, just empty records where I expect to see initials and "0" where I don't. I have debugged the function after Case 3 and in immediate window I see
    FuncEhtBy = .
    FuncEhtBy = .
    FuncEhtBy = .
    FuncEhtBy = .
    FuncEhtBy = .

    accesstos
    I replaced the function with your code and run tests on my dBase, I am getting "NotReq" for eht_stage =<2 (what I was expecting) and initials for stage 3, but everything with stages between 4-15 still returns "0", not the initials.

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Don't like that Dim DB at module level. Move it into the function.
    See the red code. No point in doing all the Select Case executions for non-matching parameter. Hopefully, only one record in the recordset will match the EHT_Stage parameter. If not, the function will pick up the last match. The debug.print will disclose any duplicates in the recordset.

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function funcEhtBy(EHT_Stage As Integer) As String
        If EHT_Stage = 0 Or EHT_Stage = 1 Or EHT_Stage = 2 Then
            funcEhtBy = "NotReq"
            Exit Function
        End If
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _
        "EHT_Reviewed_By, EHT_IFC_By  from tbl_Tracking"
    
        Set RsEHT_By = db.OpenRecordset(strEHT_By)
        If RsEHT_By.BOF Or RsEHT_By.EOF Then
            Set db = Nothing
            Set RsEHT_By = Nothing
            funcEhtBy = "TestEmpty"
            Exit Function
        End If
        With RsEHT_By
            .MoveLast
            .MoveFirst
        Do While (Not .EOF)
        If !EHT_Stage <> EHT_Stage Then
            .MoveNext
        End If
        Debug.Print "Match " & EHT_Stage
        Select Case Nz(EHT_Stage, "")
            Case 3
                funcEhtBy = Nz(!EHT_Zoned_By, "0")
            Case 4, 5
                funcEhtBy = Nz(!EHT_Designed_By, "0")
            Case 6, 9
                funcEhtBy = Nz(!EHT_Drafted_By, "0")
            Case 7
                funcEhtBy = Nz(!EHT_Extracted_By, "0")
            Case 8
                funcEhtBy = Nz(!EHT_Modeled_By, "0")
            Case 10, 11
                funcEhtBy = Nz(!EHT_Checked_By, "0")
            Case 12
                funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
            Case 13
                funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
            Case 14
                funcEhtBy = Nz(!EHT_Reviewed_By, "0")
            Case 15
                funcEhtBy = Nz(!EHT_IFC_By, "0")
            Case Else
                funcEhtBy = "TestEmpty"
      End Select
      'Choose one of below:
    '  Exit Do          'found match, so outta here
      .MoveNext     'Continue, may be more matches 
      Loop
    End With
        RsEHT_By.Close
        Set RsEHT_By = Nothing
    End Function
    
    Last edited by davegri; 10-27-2019 at 07:37 AM. Reason: More red at end, avoid endless loop

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by davegri View Post
    I think Accesstos has it right. The value returned by the function is set by every record in the recordset, but the function is not exited until the last record, so all the previous records don't matter.
    Gotcha. I misinterpreted the comment.

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by HS_1 View Post
    accesstos
    I replaced the function with your code and run tests on my dBase, I am getting "NotReq" for eht_stage =<2 (what I was expecting) and initials for stage 3, but everything with stages between 4-15 still returns "0", not the initials.
    Can you provide the "tbl_Tracking" table? I suspect that you have a "spreadsheet" style table.

    You have a field named "EHT_Stage" in "tbl_Tracking" and seems like you omitted the criteria in the expressions, like:
    Code:
    " ...WHERE EHT_Stage=" & EHT_Stage
    at the end of SQL expression of "RsEHT_By" recordset.

    or, in my code:
    Code:
    Nz(DLookup(arrFields(EHT_Stage - 3), "tbl_Tracking","EHT_Stage=" & EHT_Stage), "0")

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

    dbase

    Quote Originally Posted by accesstos View Post
    Can you provide the "tbl_Tracking" table? I suspect that you have a "spreadsheet" style table.

    You have a field named "EHT_Stage" in "tbl_Tracking" and seems like you omitted the criteria in the expressions, like:
    Code:
    " ...WHERE EHT_Stage=" & EHT_Stage
    at the end of SQL expression of "RsEHT_By" recordset.

    or, in my code:
    Code:
    Nz(DLookup(arrFields(EHT_Stage - 3), "tbl_Tracking","EHT_Stage=" & EHT_Stage), "0")
    forum.zipforum.zip
    forum.zip

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    OK, having seen the tables and able to test, this works:

    Code:
    Public Function funcEhtBy(EHT_Stage As Integer) As String
        If EHT_Stage = 0 Or EHT_Stage = 1 Or EHT_Stage = 2 Then
            funcEhtBy = "NotReq"
            Exit Function
        End If
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _
        "EHT_Reviewed_By, EHT_IFC_By  from tbl_Tracking"
        Set RsEHT_By = db.OpenRecordset(strEHT_By)
        If RsEHT_By.BOF Or RsEHT_By.EOF Then
            Set db = Nothing
            Set RsEHT_By = Nothing
            funcEhtBy = "TestEmpty"
            Exit Function
        End If
        With RsEHT_By
            .MoveLast
            .MoveFirst
            Do While (Not .EOF)
                If !EHT_Stage = EHT_Stage Then
                      'Debug.Print "Match " & EHT_Stage
                      Select Case Nz(!EHT_Stage, "")
                          Case 3
                              funcEhtBy = Nz(!EHT_Zoned_By, "0")
                          Case 4, 5
                              funcEhtBy = Nz(!EHT_Designed_By, "0")
                          Case 6, 9
                              funcEhtBy = Nz(!EHT_Drafted_By, "0")
                          Case 7
                              funcEhtBy = Nz(!EHT_Extracted_By, "0")
                          Case 8
                              funcEhtBy = Nz(!EHT_Modeled_By, "0")
                          Case 10, 11
                              funcEhtBy = Nz(!EHT_Checked_By, "0")
                          Case 12
                              funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
                          Case 13
                              funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
                          Case 14
                              funcEhtBy = Nz(!EHT_Reviewed_By, "0")
                          Case 15
                              funcEhtBy = Nz(!EHT_IFC_By, "0")
                          Case Else
                              funcEhtBy = "TestEmpty"
                    End Select
                    Exit Do
                End If
            .MoveNext
            Loop
        End With
        If Len(funcEhtBy) = 0 Then funcEhtBy = "TestEmpty"
        RsEHT_By.Close
        Set RsEHT_By = Nothing
    End Function

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

    thank you

    Quote Originally Posted by davegri View Post
    OK, having seen the tables and able to test, this works:

    Code:
    Public Function funcEhtBy(EHT_Stage As Integer) As String
        If EHT_Stage = 0 Or EHT_Stage = 1 Or EHT_Stage = 2 Then
            funcEhtBy = "NotReq"
            Exit Function
        End If
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim strEHT_By As String
        Dim RsEHT_By As DAO.Recordset
        'get initials from table
        strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _
        "EHT_Reviewed_By, EHT_IFC_By  from tbl_Tracking"
        Set RsEHT_By = db.OpenRecordset(strEHT_By)
        If RsEHT_By.BOF Or RsEHT_By.EOF Then
            Set db = Nothing
            Set RsEHT_By = Nothing
            funcEhtBy = "TestEmpty"
            Exit Function
        End If
        With RsEHT_By
            .MoveLast
            .MoveFirst
            Do While (Not .EOF)
                If !EHT_Stage = EHT_Stage Then
                      'Debug.Print "Match " & EHT_Stage
                      Select Case Nz(!EHT_Stage, "")
                          Case 3
                              funcEhtBy = Nz(!EHT_Zoned_By, "0")
                          Case 4, 5
                              funcEhtBy = Nz(!EHT_Designed_By, "0")
                          Case 6, 9
                              funcEhtBy = Nz(!EHT_Drafted_By, "0")
                          Case 7
                              funcEhtBy = Nz(!EHT_Extracted_By, "0")
                          Case 8
                              funcEhtBy = Nz(!EHT_Modeled_By, "0")
                          Case 10, 11
                              funcEhtBy = Nz(!EHT_Checked_By, "0")
                          Case 12
                              funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
                          Case 13
                              funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
                          Case 14
                              funcEhtBy = Nz(!EHT_Reviewed_By, "0")
                          Case 15
                              funcEhtBy = Nz(!EHT_IFC_By, "0")
                          Case Else
                              funcEhtBy = "TestEmpty"
                    End Select
                    Exit Do
                End If
            .MoveNext
            Loop
        End With
        If Len(funcEhtBy) = 0 Then funcEhtBy = "TestEmpty"
        RsEHT_By.Close
        Set RsEHT_By = Nothing
    End Function
    davegri
    Thank you very much for your help, I wouldn't be able to solve this problem on my own.

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. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM
  5. Replies: 3
    Last Post: 05-06-2011, 02:49 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