Windows XP 
Access 2003
Hi All
I have a search menu where users are able to search by using the name of a client. This then displays the Name, DOD and discharge date. However I would like to slightly alter it.
I'm wanting teams to be able to search only for names within their teams, however i want a second option where if the names are in the other teams i want them to be shown in a second field.
At the moment I have one list box which displays all the names, regardless of names.
I have included the VB coce that I'm using.
Private Sub Command6_Click()
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim strFill As String
Dim l As Long
Dim k As Long
Dim s As String
Set DB = CurrentDb()
strFill = ""
If Len(Me!Text4) > 0 Then
sqlStr = "SELECT [forename] & "" "" & [Surname]& "" (D.O.B.) "" & [dob] & "" (Discharge Date) "" & [DischargeDate] & "" "" AS dat, patientID FROM tblPatients WHERE ((([FOREname] &[SURNAME] & [dob]) Like ""*" & Me!Text4 & "*"")) order by [SURname]"
Debug.Print sqlStr
Set rs = DB.OpenRecordset(sqlStr)
If rs.RecordCount > 0 Then
Do Until rs.EOF
l = Len(rs!dat)
s = ""
For k = 1 To l
If Mid(rs!dat, k, 1) <> "," Then s = s & Mid(rs!dat, k, 1)
Next k
strFill = strFill & s & ";" & rs!PatientID & ";"
rs.MoveNext
Loop
Else
MsgBox "Search Text was not found", vbOKOnly, "CYPSS Database"
End If
rs.Close
End If
Set rs = Nothing
DB.Close
Set DB = Nothing
If Len(strFill) > 2048 Then stfill = Left(strFill, 2048)
Me!List0.RowSource = strFill
End Sub
Reply With Quote
