By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Trouble with SQL Statement in Function

P: n/a
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#);

Sep 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
When you form sql in code, you *always* need to force the date format into
usa format.
I would use:

format([Admitting Date],"mm/dd/yyyy")

to make life more easy, build a function like:
Public Function qudate(myDate As Date) As String

' This routine requires a DATE date type. Etiher a var, or
' a field on a form is ok.
' returns a formatted string of date, surrounded with # signs
' format is mm/dd/yyyy as required for sql, regardless of date setting

qudate = "#" & Format(myDate, "mm/dd/yyyy") & "#"
End Function
Then use:
dtStartDate = Me!StartDate
dtEndDate = Me!EndDate
strRange = "([Admission Data].[Admitting Date] >= " qudate(dtStartDate)
& _
" AND [Admission Data].[Admitting Date] <= " &
qudate(dtEndDate)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com


Sep 19 '07 #2

P: n/a
"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:qTdIi.9104$A55.1718@pd7urf2no:
I would use:

format([Admitting Date],"mm/dd/yyyy")

to make life more easy, build a function like:

Public Function qudate(myDate As Date) As String
Er, the canonical solution for this is the DateSerial() function.

However, if you're going to use format, use one of the unambiguous
ones, like "long".

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.