I would appreciate help with the following Function.
The SQL statement below returns a Recordcount = 0, yet,
when it runs in the query designer it returns the correct count of 40
Private Function ApplyFilter()
Dim intCount As Single
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim strRange As String
Dim strCondition As String
Dim strRange2 As String
Dim strCondition2 As String
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
On Error GoTo EH
Set cn = CurrentProject.Connection
dtStartDate = Me!StartDate
dtEndDate = Me!EndDate
strRange = "([Admission Data].[Admitting Date] >= #" & dtStartDate
& "# AND [Admission Data].[Admitting Date] <= #" & dtEndDate & "#)"
strCondition = "([Admission Data].[Account Number] NOT LIKE 'G%')
AND "
DoCmd.SetWarnings (True)
With cn
Set rst = New ADODB.Recordset
'get Theraputic PTT Heparin
strSQL = "SELECT [Admission Data].AdmissionID, [Admission
Data].[Admitting Date], [Admission Data].[Account Number],
Min(Labs.LabValue) AS NumLabValues"
strSQL = strSQL & " FROM ([Admission Data] LEFT JOIN Labs ON
[Admission Data].AdmissionID = Labs.AdmissionID) LEFT JOIN Medications
ON [Admission Data].AdmissionID = Medications.AdmissionID"
strSQL = strSQL & " WHERE (((Medications.[Medication
Anticoag]) Like 'Heparin')"
strSQL = strSQL & " And ((Medications.[Medication Route]) Like
'25000*') And ((Labs.[Lab Type]) = 'PTT')"
strSQL = strSQL & " And ((IsNumeric(Labs.LabValue)) = True) And
((CSng(Labs.LabValue)) >= 50.5"
strSQL = strSQL & " And (CSng(Labs.LabValue)) <= 72.4))"
strSQL = strSQL & " GROUP BY [Admission Data].AdmissionID,
[Admission Data].[Admitting Date], [Admission Data].[Account Number]"
strSQL = strSQL & " HAVING " & strCondition & strRange & ";"
rst.Open strSQL, cn, adOpenStatic, adLockReadOnly
intCount = rst.RecordCount
Me!txtTherPTT = intCount
rst.Close
End With
' clean up
cn.Close
Set rst = Nothing
Set cn = Nothing
Exit Function
EH:
' clean up
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
End If
Set cn = Nothing
If Err <0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Function
This is the query in the Query Designer
SELECT [Admission Data].AdmissionID, [Admission Data].[Admitting
Date],
[Admission Data].[Account Number], Min(Labs.LabValue) AS NumLabValues
FROM ([Admission Data] LEFT JOIN Labs ON [Admission Data].AdmissionID
= Labs.AdmissionID)
LEFT JOIN Medications ON [Admission Data].AdmissionID =
Medications.AdmissionID
WHERE ((Medications.[Medication Anticoag]) Like 'Heparin')
And (Medications.[Medication Route] Like '25000*')
And (Labs.[Lab Type] = 'PTT')
And (IsNumeric(Labs.LabValue) = True)
And (CSng(Labs.LabValue) >= 50.5 And CSng(Labs.LabValue) <= 72.4)
GROUP BY [Admission Data].AdmissionID, [Admission Data].[Admitting
Date], [Admission Data].[Account Number]
HAVING ([Admission Data].[Admitting Date] >= #4/1/2007# AND [Admission
Data].[Admitting Date] <= #4/30/2007#);
