I have a form which I allow the user to select from a multitude of options and based on what they have selected they can run a query and it outputs the query results to a listbox on my form. I have the SQL written on my form code. The reason I'm using SQL and not standard queries is because I need to have user-defined SQL.

Now I have a problem with the Count function when running it with SQL. The following example I will give I have reduced down from my actual SQL statement for the reasons of not making this post too cluttered. I have narrowed the problem down to the Count function which I'm trying to resolve. For example if I leave out the Count statement and just put:

Me.ListTotals.Rowsource = "SELECT TOP 25([tblDROP200].[MIN]), [tblDROP200].[CELL] " & _
"FROM [tblDROP200];"

This works fine and outputs the results to the listbox on the form but when I put in:

Me.ListTotals.Rowsource = "SELECT TOP 25(Count([tblDROP200].[MIN]) AS [Number of Events]), [tblDROP200].[MIN]), [tblDROP200].[CELL] " & _
"FROM [tblDROP200];"

This does not work. I've tested the Count function using a normal query and it works but when I try to use it in my SQL statement on the form code it does not. I'd really appreciate any suggestions or help. Thanks.

You cannot combine the TOP operand with a Totals query. The basic confict is that TOP values queries return detail information and a Totals query returns summary information. You will need separate queries. One to return the rows and a second to count them. You can use the Top values query as the recordsource for your Totals query.

Select Count(*) As [Number of Events]
From YourTopValuesQuery;

I think that you will need to save the query as a querydef prior to running the Totals query. Although it might work as a subselect. You can try that.

Dim strSQL as String
strSQL = "SELECT TOP 25([tblDROP200].[MIN]), [tblDROP200].[CELL] " & _
"FROM [tblDROP200]"

Select Count(*) As [Number of Events]
From (" & strSQL & ");"

Then the Rowsource will be:

Me.ListTotals.Rowsource = strSQL & ";"

Thanks Pat. I really appreciate the time you took to look at my problem. After reading that I can't do a top 10 on a Count function it occured to me that I could re-order my SQL statement and basically I got it working now (at least for now http://www.access-programmers.co.uk/ubb/wink.gif ). So thanks again!!!