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

[rdjohnso]

I have a report that is like a spreadsheet...
See screenshot at: http://www.lnrconsulting.com/temp/ResourceRpt.jpg Here

(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

 

[Rickster57]

Report

How are you going to control your data and put the values in your Jan05, Feb05, etc. across the top of your report? I think you are going to run into issues trying to do it like this. If you want it to look like Excel why not use excel?

Don't know what you are using as your Data Source for this report but you may want to use a crosstab query or use subreports to make it look like what you have displayed.

 

[rdjohnso]

Someone else suggested I do it this way....

=DCount("*",tbl_Person,"Now() Between StartDate And EndDate And GroupType='GWHIS'")

Unfortunately.. I am still getting the notorious...

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

 

[Pat Hartman]

=DCount("*","tbl_Person","Date() Between StartDate And EndDate And GroupType='GWHIS'")

1. You were missing the quotes around the table name.
2. Now() includes time of day. For this test, you need to use only the date so you would use the Date() function.