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:

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

 

[TheAceMan1]

How are ya rdjohnso . . .

Set the ControlSource to [blue]a function that returns the SQL result[/blue] . . . example

In the controlSource . . .

[blue]   =PersonCount()[/blue]

. . . and the function

[blue]Public Function PersonCount() As Long
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String

   Set db = CurrentDb
   SQL = "SELECT Count([personID]) as Cnt " & _
         "FROM tbl_Person " & _
         "WHERE (Now() Between [StartDate] And [EndDate]) And " & _
               "([tbl_Person.GroupType]='GWHIS');"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   PersonCount = rst!Cnt
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

See Ya! . . . . . .

 

[rdjohnso]

thanks Aceman.....

will it be an issue to setup a ton of functions 1 for each of the cells?

 

[Remou]

See
Can I ...... Should I... SQL Statement in Control Source of a Report
thread703-1193639
Also

 

[TheAceMan1]

Remou! . . .

;-)

See Ya! . . . . . .

 

[rdjohnso]

Remou 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?

 

[PHV]

Syntax corection:
=DCount("*",[!]"[/!]tbl_Person[!]"[/!],"Now() Between StartDate And EndDate And GroupType='GWHIS'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdjohnso]

that worked PHV.. super thanks!!!