From code to populate a recordset

[iggy22]

Hi,

I am trying to populate a recordset in
Access97 using the following code, but cannot
seem to get it to work.

The table name is "EventData" that has Type and date fields.
startdate and enddate are text boxes on a Form, for wich the user
enters the date range, and then clicks a command button to
build an Excel spreadsheet on the fly that will be populated with the
results of the recordseet.

here is the code:

Dim db As DAO.Database
Dim rst As DAO.Recordset

strSql = " SELECT EventData.EventType, Count([EventData].[EventType]) AS [Num Occurrences] FROM EventData WHERE ((EventData.Date) >= (Me!startdate)) GROUP BY EventData.EventType"


The error message that I am getting is "run time error 3061", too few paramaters. ALSO not sure if I need a semicolon right before the closing quote of strSql.

Any help is appreciated.

 

[RuralGuy]

Try the following:

strSql = " SELECT EventData.EventType, " & _
"COUNT([EventData].[EventType]) AS [Num Occurrences] " & _
"FROM EventData " & _
"WHERE EventData.Date >= #" & Me!startdate & "# " & _
"GROUP BY EventData.EventType"

...that assumes they are both Date type fields.

 

[MartijnR]

Dont' forget a ; at the end of the sql statement

 

[RuralGuy]

Hi...thanks for your reply.

But to find a date within a date range,
the following does not work (ie using a WHERE and AND)

rst.Open " SELECT EventData.EventType, " & _
"COUNT([EventData].[EventType]) AS [Num Occurrences] " & _
"FROM EventData " & _
"WHERE (EventData.Date >= #" & Me!startdate & "#) AND (EventData.Date =< #" & Me!enddate & "#) GROUP BY EventData.EventType;", cnt

any help is appreciated...Iggy.

What sort of records are being returned or is it throwing an error?
You could try:
"WHERE EventData.Date Between #" & Me!startdate & "# AND #" & Me!enddate & "#"

But your biggest problem might be that you have named your field an Access reserved word (Date)!

List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[RuralGuy]

No error is being thrown, but now my output is nil.
I am returning string and int(num occurances) data. I did change the names of the fields:

current code is:

rst.Open " SELECT EventData.EventType, " & _
"COUNT([EventData].[EventType]) AS [Num Occurrences] " & _
"FROM EventData " & _
"WHERE EventData.Dat Between #" & Me!startdat & "# AND #" & Me!enddat & "#" & _
"GROUP BY EventData.EventType;", cnt


thanks again.

Hi iggy22,
The StartDate and EndDate control names were fine, it was the Date field in the table that I was concerned about. If you remove the Count() requirement line from the SQL statement, what is returned? I don't know enough about SQL to know how that might restrict the returning recordset.

Please post in the open rather than through PM's so everyone can see and maybe an SQL guru can let us know what is happening.