Link to home
Create AccountLog in
Avatar of Dare626
Dare626Flag for United States of America

asked on

Re-Opening Access VBA Question Posted ON EE

I would like to re-visit a problem i posted a few days ago answered by one of the experts, unbknownst to me the result set was returning Member's that had a diagnosis code in 2011 that were also in 2012 which i was hoping the code would output. The original post is here:

https://www.experts-exchange.com/questions/27922267/Capture-Codes-In-2011-That-Were-Not-Captured-in-2012.html

So for example if there's a HCC_Code 55 for a member in file ALLCAREP_ACCEPT_2012 and the same one IN ALLCAREP_Result_2011 it should not return it. THe Code works great its just returning codes that are in both tables that should be excluded. But the concept is correct.

I've also illustrated by problem in VISIO for a visual. Sorry IrongSinta For reposting. I just noticed it after carefully analyzing the output.
Private Sub GetNonCaptures()
    Dim rst11 As DAO.Recordset
    Dim rst12 As DAO.Recordset
    Dim fld As Field
    Dim strNonCapture
    Dim fileNum
    
    Set rst11 = CurrentDb.OpenRecordset("AllCareP_Result_2011-1")
    Set rst12 = CurrentDb.OpenRecordset("Select * From AllCareP_Accept_2012-1")
    
    fileNum = FreeFile()
    Open "C:\Documents and Settings\jquijano\Desktop\to check\HCC_Bucket_Results.txt" For Output As fileNum

    Do While Not rst11.EOF
        strNonCapture = ""
        For Each fld In rst11.Fields
            If fld.Name Like "HCC_CD*" And fld.Value = "YES" Then
                      rst12.FindFirst "[MEM_NO]='" & rst11!MEM_NO & "' AND [HCC_CD]='" & Mid(fld.Name, 7) & "'"
                If rst12.NoMatch Then
                    strNonCapture = strNonCapture & ", " & fld.Name
                End If
            End If
        Next
        strNonCapture = Mid(strNonCapture, 3)
        If strNonCapture <> "" Then
            Print #fileNum, "MEMBER " & rst11!MEM_NO & " DID NOT HAVE " & strNonCapture & " CAPTURED IN 2012"
        End If
        rst11.MoveNext
    Loop
    Close #fileNum
    
End Sub

Open in new window

Avatar of Dare626
Dare626
Flag of United States of America image

ASKER

I forgot to attach the visualization of the problem. But here it is. and correction on my original post which reads:

I would like to re-visit a problem i posted a few days ago answered by one of the experts, unbknownst to me the result set was returning Member's that had a diagnosis code in 2011 that were also in 2012 which i was hoping the code would output.

Should of read: "....2012 which i was hoping the code would NOT output that specific code.
Avatar of IrogSinta
Hmm, the code looks fine.  Can you give me some sample data that is not working?
Avatar of Dare626

ASKER

I posted an illustration im not at home so i cant post sample data right now, but if you need it i can do it first thing in the morning.

Thansk Irog.
Issue.jpg
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Dare626

ASKER

Hello IrogSinta you were right i got the correct results after placing the brackets on the table. Thanks alot for that oversight i did on my end. Thank you alot.