additional code to filter results of macro needed

[Mike]

hello I am in need of additional code to filter the results I am getting for
my macro. right now the code creates a list of all diagnoses and ICD codes
for a specific patient according to the SSN. I want to filter this list so
that it only includes Active diagnoses and omits the inactive ones. code as
of now is:

Public Function ListDiagnoses(TheSSN As String, TheDateofVisit As String) As
String
Dim rst As DAO.Recordset
Dim DiagnosesList As String
Set rst = CurrentDb.OpenRecordset("SELECT SSN, Diagnosis,
DateOfVisit, ICD FROM [Diagnosis/Procedure] WHERE SSN='" & TheSSN & "' And
DateOfVisit=#" & TheDateofVisit & "#")
With rst
Do Until .EOF
DiagnosesList = DiagnosesList & ![diagnosis] & ", " & ![ICD]
& "; "
.MoveNext
Loop
End With
If Len(DiagnosesList) Then
DiagnosesList = Left(DiagnosesList, Len(DiagnosesList) - 2)
End If
ListDiagnoses = DiagnosesList
End Function


I need to add to this code the Status column of my table and use it to
filter the results of the macro. i thought that the additions seen below
would accomplish what I need, but all I was able to get were errors for the
code


Public Function ListDiagnoses(TheSSN As String, TheDateofVisit As String) As
String
Dim rst As DAO.Recordset
Dim DiagnosesList As String
Set rst = CurrentDb.OpenRecordset("SELECT SSN, Diagnosis,
DateOfVisit, ICD, Status FROM [Diagnosis/Procedure] WHERE SSN='" & TheSSN &
"' And DateOfVisit=#" & TheDateofVisit & "#")
With rst
Do Until .EOF
[Status] = "Active"
DiagnosesList = DiagnosesList & ![diagnosis] & ", " & ![ICD]
& "; "
.MoveNext
Loop
End If
End With
If Len(DiagnosesList) Then
DiagnosesList = Left(DiagnosesList, Len(DiagnosesList) - 2)
End If
ListDiagnoses = DiagnosesList
End Function




thanks for the help in advance ~Mike

 

[Mike]

Wow I looked way to far into this problem, took a step back and nailed it.
no need to reply to this ~MIke