How do I use the COUNT function

[Guest_imported]

I'm not really sure where I went wrong, but, here's my problem. I'm trying to set up a report using the results returned from the COUNT function. I placed the following code in the OnOpen code module:

Me.RecordSource = "SELECT COUNT([City or County]) AS [City] FROM [Accident Data]" WHERE [City or County]='City'

mCity = [City]

I keep getting an error message:

Main Switchboard can't find the field 'City' referred to in your expression.

What is the correct way to get the results that I'm looking for.

 

[ghubbell]

Hi Gene,
Try this:
Me.RecordSource = "SELECT Count([Accident Data].[City or Country]) AS MCity, [Accident Data].[City or Country] FROM [Accident Data] GROUP BY [Accident Data].[City or Country] HAVING ((([Accident Data].[City or Country])='City'))"

I'm guessing mCity is a text box on your report. You'll have to type in to its control source property: Mcity .

Hope this works for you!
Gord
ghubbell@total.net

 

[Guest_imported]

Thanks for the suggestion Gord, but, I'm still getting the same error message. I don't think that I explained what I was trying to do thoroughly enough. At the beginning of the module, I defined mCity as a public variable. I then want to store the value of the COUNT into the mCity variable. I'm new to Access and VBA (my background is in FoxPro and DBase.)

 

[ghubbell]

Quick & easy:

Option Compare Database
Option Explicit

Dim mlgCity As Long
Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
Dim Rs As Recordset, Db As database
SQL = "SELECT Count([Accident Data].[City or Country]) AS MCity, [Accident Data].[City or Country] FROM [Accident Data] GROUP BY [Accident Data].[City or Country] HAVING ((([Accident Data].[City or Country])='City'))"
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
If Rs.RecordCount = 0 Then
Rs.Close
Db.Close
Exit Sub
End If
mlgCity = Rs!Mcity
Rs.Close
MsgBox "MCity = " & mlgCity, vbInformation, "Your module level variable!" 'remove if you're happy
End Sub

If you're using 2000 you'll have to have a reference to DAO 3.6 Object library: VB environment, Tools-References, and try to compile. If it won't, move its priority up 1 or 2. Try that!

Gord
ghubbell@total.net

 

[Guest_imported]

Thanks Gord, the code worked. I'm also going to be counting other fields. Will I have to write a separate set of code for each value that I'll be looking for. Or is there any to combine everything into one SUB.

 

[ghubbell]

Hi Gene, Glad to hear this worked. If you are going to have to repeat over and over, you are wise to seperate it and make it a function on its own. There are many ways to approach this, a question in "setting up" the function is would you be asking count info from the same table each time, or from different tables? I can see immediately you would want the same count on 'country', so criteria will always be an inputted variable. If from one table, we would only need the field name as an input. If from different tables, we would need table names too. Look over all your possibilities and let me know what you find. Until later,
Gord
ghubbell@total.net