Can I ...... Should I... SQL Statement in Control Source of a Report 1

[rdjohnso]

I have a report that is like a spreadsheet...
See screenshot at:

(I know it looks like Excel... but has to be like this...)

Each cell has a diff equation based upon the data in the tables... (I didnt want to have to build a seperate query view for each cell so I fifgured I could just put a SQL statement in the control source but it keeps erroring on me.... Can I not do this? What options do I have?
(I have tried alot of syntax options....)

Here is one of the SQL Statements I tried to use.

=SELECT Count([tbl_Person.personID]) FROM tbl_Person WHERE (((Now() Between [tbl_Person.StartDate] And [tbl_Person.EndDate]) And [tbl_Person.GroupType]="GWHIS"));

Thanks in advance for your input.

Ron

 

[Remou]

You cannot use the sql statement above, however you can use functions such as DCount, and DSum. For example:
[tt]=DCount("*",tbl_Person,"Now() Between StartDate And EndDate And GroupType='GWHIS'")[/tt]

 

[rdjohnso]

thanks Remou.. will give that a try...

R

 

[rdjohnso]

I tried that Remou.... I am still getting the notorious...

#Name? error.... any idea why?

R

 

[Remou]

Oops, forgot the quotes.

[tt]=DCount("*",[red]"[/red]tbl_Person[red]"[/red],"Now() Between StartDate And EndDate And GroupType='GWHIS'")

 

[rdjohnso]

thanks Remou.. that works!!!