Too Few Parameters Expected Error

[sross81]

Hello,

I have a function I built that takes in values from a form. I am trying to add an additional sql statement to it so that I can have two separate record sets. The mainsql is the main recordset that I want the strSql recordset to be able to take from. I keep getting a Run Time Error Too few parameters expected. Expected 4, Run-time error '3061'. I can't figure out what other parameters it is looking for. I have confirmed that all of the parameters that I pass in are being passed in when I stepped through.

I bolded the section I get the error on.

Thank you in advance.

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 Integer

mainsql = "SELECT tble_TempLOSDate.Facility, tble_TempLOSDate.[Pt Acct Nbr], tble_TempLOSDate.Dispositions, tble_TempLOSDate.TL, tble_TempLOSDate.[AdmitDate&Time], tble_TempLOSDate.[DischargeDate&Time], tble_TempLOSDate.LOS, tble_TempLOSDate.DispID, tble_TempLOSDate.FacilityID " & _
"FROM tble_TempLOSDate " & _
"WHERE (((tble_TempLOSDate.[AdmitDate&Time]) Between [pStartDate] And [pEndDate]) AND ((tble_TempLOSDate.DispID)=pDisposition) AND ((tble_TempLOSDate.FacilityID)=pFacility));"

Set mainrs = CurrentDb.OpenRecordset(mainsql)
rs.MoveLast
ntotal = mainrs.RecordCount


strSQL = "SELECT " & LOS & " from " & mainsql & " WHERE " & LOS & ">=0 Order by " & 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(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianAutoF = sglHold / 2
End If
rs.Close
End Function

 

[spikepl]

Use only alphanumerics and underscore for naming objects (tables, queries, forms) and fields/controls. http://support.microsoft.com/kb/826763

For checking SQL get it out like this, http://www.baldyweb.com/ImmediateWindow.htm , paste it into the SQL view of the query builder, and make it work there. Then you know what your code should deliver as SQL.

 

[sross81]

Thank you for replying. I updated the names in my table so that they no longer have spaces or special characters. I still get the error though.

I commented out the Where clause and I am able to get past this error...to another one that I will address later ....so I decided to comment out part of the where statement and see what part is causing the problem. It seems like it has to do with how I reference the dates. Is there a special character I need to use to reference dates in vb sql?

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 Integer

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


Set mainrs = CurrentDb.OpenRecordset(mainsql)
rs.MoveLast
ntotal = mainrs.RecordCount


strSQL = "SELECT " & LOS & " from " & mainsql & " WHERE " & LOS & ">=0 Order by " & 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

 

[spikepl]

It seems like it has to do with how I reference the dates. Is there a special character I need to use to reference dates in vb sql?

In VB, SQL , yes, if you need to provide a strign containing a date: #01/01/2012# look in the documentation, google examples for basics like this

In any case:

For checking SQL get it out like this, http://www.baldyweb.com/ImmediateWindow.htm , paste it into the SQL view of the query builder, and make it work there. Then you know what your code should deliver as SQL.

 

[sross81]

I like this! I never knew I could do that

So if it says between pStartDate and pEndDate instead of the dates I know that were passed into the function that is how I know that it isn't actually using the dates correctly in the sql statement. It should say the actual date there. That is probably why it says too few parameters because it thinks it needs values still for those names? I think I found some examples for that. This debugging tip is so helpful!

debug.Print mainsql

SELECT tble_TempLOSDate.PtAcctNbr, tble_TempLOSDate.AdmitDateTime, tble_TempLOSDate.LOS, tble_TempLOSDate.DispID, tble_TempLOSDate.FacilityID FROM tble_TempLOSDate WHERE (((tble_TempLOSDate.AdmitDateTime) Between pStartDate And pEndDate);

 

[sross81]

This is how I finally formatted my date reference to get rid of the too few parameters expected error in case anyone needs it .


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