Error #Name for source code

[teqtaq]

I am having Report based on query.
I need to have summary and I am trying to insert text box where data source looks like following:

=(Select Count([Table1].[EMPLID]FROM TABLE1 WHERE [Table1].[COUNTRY]='FR')

I am placing this in the group footer and it gives me the #Name error.

Can you see why? Thanks

 

[HarleyQuinn]

You have inconsistent parenthesees

=(Select Count([Table1].[EMPLID][red])[/red] FROM TABLE1 WHERE [Table1].[COUNTRY]='FR')

I'd also put in a space before the FROM keyword.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[teqtaq]

Sorry, it does the same still...

 

[dhookom]

You can't use a SQL statement as the Control Source of a text box. Either use a subreport to display the value or try:

 =DCount("[EMPLID]","TABLE1","[COUNTRY]='FR'")

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

It works except it does not care where it placed. It gives me Grand Total and I am looking for Total per Group.

Thanks, I will use it for Grand Totals.
I am wondering if there is a way to let it know - 'we are in Group'? )

 

[dhookom]

Use a subreport and link master/child on the "GROUP". Or tell us something about the "group"? I expect there is a field name and data type that we don't know about. Does table1 contain the field group?

I would probably create a totals query like:

  SELECT [GroupField], Count(Emplid) as EmpCount
  FROM Table1
  WHERE Country = "FR"
  GROUP BY [GroupField];

Then add this query to your report's record source query and join the [GroupField]s. Then rather than using DCount() or any other expression, just use a text box bound to EmpCount.

FROM

Duane
Hook'D on Access
MS Access MVP