Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Dear June7,


    Sorry to bother you again, not sure if I can ask question on Solved tread. Your code is working as I requested. However I realized I gave a wrong requirement. In my samplefile. SampleID 555 has positive values for both BdV_ct and RV_Ct. In the results it should show both Bdv, RV. How can I concatenate all the positive values field names? Current code overwrite the previous positive target.

    Thank you

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

    Result = Result & Left(rs(x).name, InStr(rs(x).name, "_") - 1) & ","
    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.

  3. #18
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    It is concatenating all the values including "Negative" for all the sampleId's.
    for Ex: SampleID=555 shows results value as NegativeBdV,RV
    For SAmpleID=123 ; results value is NegativeRV

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try revised code:
    Code:
    Function Result(strID As String) As String
    Dim rs As DAO.Recordset, x As Integer, strResult As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Samples WHERE SampleID = '" & strID & "'")
    Result = IIf(IsNumeric(strID), "Negative", "Not Valid")
    For x = 1 To rs.Fields.Count - 1
        Select Case strID
            Case "HS"
                If (rs(x) = 0 And rs(x).name <> "RNP_Ct") Or (rs(x) > 0 And rs(x).name = "RNP_Ct") Then
                    strResult = "Valid"
                End If
            Case "PC"
                If rs(x) > 0 Then
                    strResult = "Valid"
                End If
            Case "NTC"
                If rs(x) = 0 Then
                    strResult = "Valid"
                End If
            Case Else
                If (Nz(rs(x), 0) > 0 And rs(x).name <> "RNP_Ct") Then
                    strResult = strResult & Left(rs(x).name, InStr(rs(x).name, "_") - 1) & ","
                End If
        End Select
     Next
    If strResult <> "" Then
        If InStr(strResult, ",") > 0 Then
            Result = Left(strResult, InStrRev(strResult, ",") - 1)
        Else
            Result = strResult
        End If
    End If
    End Function
    

    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.

  5. #20
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you so much June7. It works perfectly as desired
    Initially I thought of using Array by declaring all the column names as global variables and looping them but CASE statement worked pretty well.

    Thank you

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-26-2016, 09:15 AM
  2. Optimizing MS Access Query
    By mariost in forum Queries
    Replies: 3
    Last Post: 11-27-2015, 04:13 AM
  3. SQL code help (Case)
    By alka in forum Access
    Replies: 10
    Last Post: 11-26-2013, 06:15 PM
  4. Help optimizing a query
    By mkallover in forum Queries
    Replies: 0
    Last Post: 03-01-2012, 09:13 AM
  5. Help optimizing an ugly query
    By kman42 in forum Queries
    Replies: 2
    Last Post: 07-26-2011, 07:37 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