Error 3061; Yet the code is the same one I always use

[Sylvialn]

Here's what I have - I have one report (Final) which contains 2 subreports.
Subreport1: lists a record: Name, Event, Days (one type)
Subreport2: lists a record: Name, Event, Days (another query)
On the Final report, I need to display the count of each different event from the subreports. I have written an SQL Sub in the Final Report (On Open event). But, it gives me an error ("Runtime Error 3061: too few parameters. Expected 2.&quot and highlights the line: Set rstQry = dbs.OpenRecordset("qryRptFacPtsSubOne&quot. But I can't figure out what else I need. I have written these types of SQL cases and have done it this ways each time. Here's the code in the report:
........
Dim strEvent As String
Dim dbs As Database
Dim rstQry As Recordset......
Set dbs = CurrentDb
Set rstQry = dbs.OpenRecordset("qryRptFacPtsSubOne&quot
With rstQry
rstQry.MoveFirst
Do While Not rstQry.EOF
strEvent = rstQry!EventCode
' Set lookup variables to count events
Select Case strEvent
Case "2"
TwoCount = TwoCount + 1
..........
Case Else
'error
End Select
rstQry.MoveNext
Loop
End With
Me!EventTwo = TwoCount.....
End Sub

The query it is referencing does have one field that is based off of a lengthy if/else, but it has no problems running by itself - no errors. Any suggestions? "Try everything at least once."

 

[scking]

Your problem is most probably in qryRptFacPtsSubOne. This error generally indicates that some element of the query is unable to find the source field indicated. Check your IIF VERY CAREFULLY and you will probably find a mistyped field name or something. This can happen when either the table is changed, a source query is changed, or a back-end database is replaced. Sometimes they are very hard to find because they are too easy to overlook.

You might try creating a new query that displays all the values in the IIF and then try running it.

Steve King Growth follows a healthy professional curiosity

 

[Sylvialn]

Thanks so much....I'll try that! "Try everything at least once."

 

[Sylvialn]

When I run the query in the queries tab, I recieve no errors. Why does it let me run the query here, and not run through the code?? PREPARE YOURSELF: Here's what it looks like:
SELECT qryFacPtsOne.AdmitDate, qryFacPtsOne.PatID, qryFacPtsOne.PatName, qryFacPtsOne.PatTeam, qryFacPtsOne.DiagCode1, qryFacPtsOne.EventCode, qryFacPtsOne.DateStart, qryFacPtsOne.DateEnd, qryPatDeathDisch.DateTerm, IIf((IsNull([DateTerm]) And ([DateStart]<[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[Forms]![DateRange]![DateStart],[Forms]![DateRange]![DateEnd]),IIf((IsNull([DateTerm]) And ([DateStart]>=[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[DateStart],[Forms]![DateRange]![DateEnd]),IIf((([DateTerm]<=[Forms]![DateRange]![DateEnd]) And ([DateStart]<[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[Forms]![DateRange]![DateStart],[DateTerm]),IIf((([DateTerm]<=[Forms]![DateRange]![DateStart]) And ([DateStart]>=[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[Forms]![DateRange]![DateStart],[DateTerm]),IIf((([DateTerm]<[Forms]![DateRange]![DateEnd]) And ([DateStart]>=[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[DateStart],[DateTerm]),IIf((([DateTerm]>[Forms]![DateRange]![DateEnd]) And ([DateStart]<[Forms]![DateRange]![DateStart])),DateDiff(&quot;d&quot;,[Forms]![DateRange]![DateStart],[Forms]![DateRange]![DateEnd]),DateDiff(&quot;d&quot;,[DateStart],[Forms]![DateRange]![DateEnd])))))))+1 AS LOS
FROM qryFacPtsOne LEFT JOIN qryPatDeathDisch ON qryFacPtsOne.PatID = qryPatDeathDisch.PatID
WHERE (((qryFacPtsOne.DateStart)<=[Forms]![DateRange]![DateEnd]) AND ((qryPatDeathDisch.DateTerm) Is Null Or (qryPatDeathDisch.DateTerm)>=[Forms]![DateRange]![DateStart]));

Maybe my logic should be fixed :-0 !! The LOS field calcualtes length of stay. I couldn't think of how else to do it! I know, I know....these novices!!!! But...can you help? &quot;Try everything at least once.&quot;

 

[scking]

If you can run the query from the query tab in the database window but not through the code how do you invoke it through the code? This would only be one way ---

'USE THE QUERY TO BUILD A RECORDSET
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;qryFromDbWindow&quot

Steve King



Growth follows a healthy professional curiosity

 

[Sylvialn]

What's the difference between:
Dim dbs As Database
Dim rstQry As Recordset

and what you have written:
Dim db As DAO.Database
Dim rst As DAO.Recordset

using the DAO???? &quot;Try everything at least once.&quot;

 

[scking]

Access 97 uses Data Access Objects (DAO) as the default database access technology and Access 2000 uses ActiveX Data Objects (ADO) as the default. When I use DAO in from the the database or recordset I am telling the system that whatever database I am currently using use DAO. This prevents having to modify code when moving from 97 to 2000 and is also explicit. It will work in Access 2000 if the DAO 3.6 Compatability reference is checked.

This can easily cause confusion since DAO and ADO do not use the same properties and methods. During conversion from 97 to 2000 Access will not convert from DAO to ADO and when the application is run syntax errors will result unless you are explicit or modify all your code to comply with ADO.

Steve King Growth follows a healthy professional curiosity