How to Open a RecordSet - No records loading

[sross81]

Hello,

I have spent all day debugging issues having to do with passing variables to my function. I finally know that the variables are passing and the sql statement is formatted correctly.

The main problem I am having is that when I create the recordset no matter how I do it whether I use the variables I pass in or if I hard code the variables in I do not think its actually returning any results.

Is there something I am missing to actually load the recordset?

Function MedianAutoF(pDisposition As String, pFacility As String, pStartDate As Date, pEndDate As Date) As String

Dim rs As Recordset
Dim strSQL As String
Dim n As Long
Dim sglHold As Long
Dim mainrs As Recordset
Dim mainsql As String
Dim ntotal As Long

mainsql = "SELECT tble_TempLOSData.PtAcctNbr, tble_TempLOSData.AdmitDateTime, tble_TempLOSData.LOS, tble_TempLOSData.DispID, tble_TempLOSData.FacilityID " & _
"FROM tble_TempLOSData " & _
"WHERE (((tble_TempLOSData.AdmitDateTime) Between #" & pStartDate & "# And #" & pEndDate & "#)" & _
"AND ((tble_TempLOSData.DispID)=" & pDisposition & ") AND ((tble_TempLOSData.FacilityID)=" & pFacility & "));"

'Testing to see if any records come back always get false
Set mainrs = CurrentDb.OpenRecordset(mainsql)
If mainrs.RecordCount > 1 Then
Debug.Print "true"
Else
Debug.Print "false"
End If


'mainsql = "SELECT tble_TempLOSData.PtAcctNbr, tble_TempLOSData.AdmitDateTime, tble_TempLOSData.LOS, tble_TempLOSData.DispID, tble_TempLOSData.FacilityID INTO TEMPTABLE " & _
'"FROM tble_TempLOSData " & _
'"WHERE (((tble_TempLOSData.AdmitDateTime) Between #" & pStartDate & "# And #" & pEndDate & "#)" & _
'" AND ((tble_TempLOSData.DispID)=" & pDisposition & ") AND ((tble_TempLOSData.FacilityID)=" & pFacility & "));"


'strSQL = "SELECT * FROM TEMPTABLE WHERE " & TempTable.LOS & ">=0 Order by " & TempTable.LOS & ";"
'Set rs = CurrentDb.OpenRecordset(strSQL)
'rs.MoveLast
'n = rs.RecordCount
'rs.Move -Int(n / 2)

'If n Mod 2 = 1 Then 'odd number of elements
' MedianAutoF = rs(LOS)
'Else 'even number of elements
' sglHold = rs(LOS)
' rs.MoveNext
'sglHold = sglHold + rs(LOS)
'MedianAutoF = sglHold / 2
'End If
'rs.Close
End Function

 

[pbaldy]

A better test:

If mainrs.EOF Then

That will be True if the recordset is empty. You can test the SQL with this:

http://www.baldyweb.com/ImmediateWindow.htm

 

[bob fitz]

Could the problem be with :

"AND ((tble_TempLOSData.DispID)=" & pDisposition & ") AND ((tble_TempLOSData.FacilityID)=" & pFacility & "));"

Both pDisposition and pFacility are declared as strings. Just wondered if they should be numbers or should the line of code be:

]"AND ((tble_TempLOSData.DispID[COLOR=black])= [B][COLOR=red]'[/COLOR][/B]"[/COLOR] & pDisposition & [COLOR=black]"[B][COLOR=red]'[/COLOR][/B])[/COLOR] AND ((tble_TempLOSData.FacilityID)= [B][COLOR=red]'[/COLOR][/B]" & pFacility & "[B][COLOR=red]'[/COLOR][/B]));"

 

[sross81]

Thank you both.

First I checked the pFacility and pDisposition because Bob mentioned how I passed those as strings and I didn't represent that in my sql statement. They are actually numbers so I fixed that.

Next I just tested it with >0 instead of 1 and I got the result of True so that must mean I have some data, but when I left the 1 in there it was false.

How can I make it display the actual data in like a data sheet view for example that it opens in the record set? I have been trying to find that too. I find some examples of querydef, but I didn't have alot of luck with it.

Thank you

Function MedianAutoF(pDisposition As Integer, pFacility As Integer, pStartDate As Date, pEndDate As Date) As String

Dim rs As Recordset
Dim strSQL As String
Dim n As Long
Dim sglHold As Long
Dim mainrs As Recordset
Dim mainsql As String
Dim ntotal As Long

mainsql = "SELECT tble_TempLOSData.PtAcctNbr, tble_TempLOSData.AdmitDateTime, tble_TempLOSData.LOS, tble_TempLOSData.DispID, tble_TempLOSData.FacilityID " & _
"FROM tble_TempLOSData " & _
"WHERE (((tble_TempLOSData.AdmitDateTime) Between #" & pStartDate & "# And #" & pEndDate & "#)" & _
"AND ((tble_TempLOSData.DispID)=" & pDisposition & ") AND ((tble_TempLOSData.FacilityID)=" & pFacility & "));"

Set mainrs = CurrentDb.OpenRecordset(mainsql)
If mainrs.RecordCount > 0 Then
Debug.Print "true"
Else
Debug.Print "false"
End If

Sherri

 

[sross81]

I just tested another querydef example and it works. It must not have worked before because of the strings in my variables that I passed in? Not sure.


Function MedianAutoF(pDisposition As Integer, pFacility As Integer, pStartDate As Date, pEndDate As Date) As String

Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String
Dim n As Long
Dim sglHold As Long
Dim mainrs As Recordset
Dim mainsql As String
Dim qdf As QueryDef
Set dbs = CurrentDb()
mainsql = "SELECT tble_TempLOSData.PtAcctNbr, tble_TempLOSData.AdmitDateTime, tble_TempLOSData.LOS, tble_TempLOSData.DispID, tble_TempLOSData.FacilityID " & _
"FROM tble_TempLOSData " & _
"WHERE (((tble_TempLOSData.AdmitDateTime) Between #" & pStartDate & "# And #" & pEndDate & "#)" & _
"AND ((tble_TempLOSData.DispID)=" & pDisposition & ") AND ((tble_TempLOSData.FacilityID)=" & pFacility & "));"
'this opens a data sheet view
Set mainrs = CurrentDb.OpenRecordset(mainsql, dbOpenSnapshot)
With dbs
Set qdf = .CreateQueryDef("tmpLOSInfo", mainsql)
DoCmd.OpenQuery "tmpLOSInfo"
.QueryDefs.Delete "tmpLOSInfo"
End With

If mainrs.RecordCount > 0 Then
Debug.Print "true"
Else
Debug.Print "false"
End If