"Too few parameters. Expected 1" :(

[bf2mad]

Hi,

I am using Access 2000 and have a problem with Recordsets and a QueryDef. No matter what I try whenever I run the code I keep on getting "Too few parameters. Expected 1"

After a long time reading past treads I have discoved that the problem is normally becouse I am tring to open a rescordset to a query that refers to a form which mine does and therfore my query is no longer a select query it is now a QueryDef.

In my code I now have a string that stores my stl statment (SqlStr) and my open recordset now referes to that but I still get the same message.

Heres my Code:

Dim DB As DAO.Database
Dim myRecord As DAO.Recordset

Set DB = CurrentDb()
Dim SqlStr As String

SqlStr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)=[forms]![Switchboard]![cobName]) AND ((Main.Date)=Date()));"

Set myRecord = DB.OpenRecordset(SqlStr)

With myRecord
If .RecordCount Then .MoveFirst
Do Until myRecord.EOF
If ![Action] = "Resolved" Then
Counter(1) = Counter(1) + 1
ElseIf ![Action] = "Reassigned" Then
Counter(2) = Counter(2) + 1
ElseIf ![Action] = "Rejected" Then
Counter(3) = Counter(3) + 1
ElseIf ![Action] = "Handled" Then
Counter(4) = Counter(4) + 1
End If
Loop
End With
Form_DataSheet.labResolved.Caption = Counter(1)
Form_DataSheet.labReassigned.Caption = Counter(2)
Form_DataSheet.labRejected.Caption = Counter(3)
Form_DataSheet.labHandled.Caption = Counter(4)



And if you need it here is the query I tried to use is bassically just brings up the records for a certain persion(combo box on a form) and for the current date.

SELECT Main.Name, Main.Date, Main.[Reference Number], Main.Account, Main.Action, Main.[R\R Reason], Main.Time, Main.[Approx Time], Main.Group, Main.CallOver20
FROM Main
WHERE (((Main.Name)=[forms]![Switchboard]![cobName]) AND ((Main.Date)=Format(Date(),"Short Date&quot))
ORDER BY Main.Time;



Please plesae please can you help it's driving me crazy.

Many Thanks

Phil

 

[JeremyNYC]

OK, there are a few places to look. The first two are the names of your fields: both "name" and "date" are access reserved words, and should never be used as the names of objects in Access.

The other place to look is one you referenced, but from what you said I'm not sure where you stand on that: the issue of referencing a form. Is that form ope4n when you run this code? If that form isn't open (or if the reference to the control on that form is incorrect), this sql statement won't work. Also, I don't think it will work to have the reference inside your quotes. I think you'll have to do this:
SqlStr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)='" & [forms]![Switchboard]![cobName] & "') AND ((Main.Date)=Date()));"

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[bf2mad]

Thanks.

I have changed the field names toagentNames and entryDate and also chaged the SqlStr to what you suggested and that seems to have sorted my origonal problem (I am finally a step further) but I am now getting a "Run-time error '6': Overflow" on the fine in my code

Counter(1) = Counter(1) + 1

posted above and can't work it out as in my recordset there is only one record witht eh action resolved.

Any ideas

Thanks again

 

[bf2mad]

Also I did a record count on this record set and it is showing three records which is all of the records for this person not just the ones for today for some reason the SQL statment is not displaying records just for today it is displaying record for the person for all dates.

The SQL statment should display all records where entryDate = today (then end part of teh statment

 

[JeremyNYC]

Zoink! Check out your loop. You hang out on the same record every time through. This means that it's just looping though until Access can't count any higher.

Before the Loop line, put in .moveNext, which will scoot you on to the next record.

Jeremy

PS: Now that I'm looking at this, I'm a little confused. Why not make a recordset that groups by Action and counts by some other field? It seems like that would get you the same data as what you're doing now, and it would certainly be a lot faster.

PPS: If you need to do it this way, you might consider a Case/Select structure, which is a little cleaner than the if/then/elseif structrue.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[bf2mad]

Thanks for all your help seems to be working (soft of) I just have the date probelm to sort out.

Thanks

 

[wjmsm]

Remove the semi-colon ";" from the end of your assignment statement so that SqlStr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)=[forms]![Switchboard]![cobName]) AND ((Main.Date)=Date()))"

 

[Rick39]

Try
sqlstr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)=[forms]![Switchboard]![cobName]) AND ((Main.Date)= " & Date & ")"
'test it here
MsgBox sqlstr

 

[Rick39]

Oops - Should be
sqlstr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)='" & [forms]![Switchboard]![cobName]& "') AND ((Main.Date)= " & Date & ")"
'test it here
MsgBox sqlstr

 

[Databaseguy]

If you dimmed counter as an interger, Counter(1) = Counter(1) + 1 will error out on you once it gets past 32,000 and change......

Declare it as a single or double instead.
Tyrone Lumley
augerinn@gte.net

 

[JeremyNYC]

sqlstr = "SELECT Main.Name, Main.Date, Main.Action FROM Main WHERE (((Main.Name)='" & [forms]![Switchboard]![cobName]& "') AND ((Main.Date)= " & Date & ")"

should fail, b/c the date needs to be offset in #s, and because there is text after the semicolon (which isn't really needed anyway). Try this:
sqlstr = "SELECT AgentNames, EntryDate, Action FROM Main WHERE (((AgentNames)='" & [forms]![Switchboard]![cobName]& "') AND ((EntryDate)= #" & Date & "#))"

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.