SQL column not recognized in report.

[Guest_imported]

I am trying to set up a report using a select statement that starts off something like the following:

Set Rs = Db.OpenRecord("Select Count([Field Name]) As NewField....

When I refer to the field NewField in the report I get an error indicating that there is no such field. How do I set something like this up? Is there a Dim statement or some other step that I should do prior to running the SQL statement?

 

[Rolliee]

I believe the output of your SQL statement is a number, the number of the records meeting your criteria.

 

[Steve101]

Try the following:

DIM DB as Database: SET DB = CurrentDB()
DIM RS as RecordSet
SET RS = DB.OpenRecordSet("Select Count([Field Name] AS FiendNameCount&quot
NewField = RS!FieldNameCount
RS.Close: DB.Close

Its not the easiest way of doing it, but its close to your example, so should help you to understand the syntax more easily.



Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Guest_imported]

From what I see here, everything I've done is ok. But after the Select COUNT(FieldName) As NewField..., when I refer to the new column NewField it's not recognized. In the report, I'm grouping on certain fields and I want the field NewField to show a total count for that group. I have tried referencing the new column as: NewField, Rs.NewField and [NewField], but nothing seems to work. I'm not really an Access/VBA programmer, but I am familiar with SQL syntax. What am I doing wrong?

 

[Steve101]

Dont know enough about your report, but perhaps you should be working off the fields associated with the record source property of the report. This, in conjunction with having associated Group total fields defined in the report should give you what you want. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Rolliee]

If you only want the number of records in your query, why do you not use the function dcount and not open a recordset only to close it?

newfield = DCount("[FieldName]", "collections&quot

P.S. collections is the tablename and fieldname is the name.