Use queries in reports

[beacon]

Can you use queries in reports? I'm trying to enter a SQL statement in a text box on a report, but if I do it with an equal sign I get a syntax error. If I do it without an equal sign I get an invalid control source.

If this is possible, where am I going wrong?

Here's my SQL statement:

SELECT CountOfSkillLevel FROM qrySkillsWF WHERE SkillLevelID = 1

Thanks for the help

[nico5038]

Can you use queries in reports? I'm trying to enter a SQL statement in a text box on a report, but if I do it with an equal sign I get a syntax error. If I do it without an equal sign I get an invalid control source.

If this is possible, where am I going wrong?

Here's my SQL statement:

SELECT CountOfSkillLevel FROM qrySkillsWF WHERE SkillLevelID = 1

Thanks for the help

To get the data out of a table you'll need the DLookup() function like:

=DLookup("CountOfSkillLevel","qrySkillsWF","SkillLevelID = 1")

Nic;o)

[NeoPa]

You won't need the query though. You can also use DCount() on the underlying table for similar results (assuming of course that the counting is as simple as it would appear).

[beacon]

I tried the DLookup in the report, but it's not returning a value. It's not even returning an error. I typed it in exactly as shown, checked the names of the tables and columns, and it is coming back blank.

Any ideas what I'm doing wrong? Does it make a difference that it's on a report?

[beacon]

Nevermind...I got it to work. Thanks for the help you guys.

[NeoPa]

I'm not clear exactly what you've tried.
How about you post the current SQL of the query the report is built upon, and any relevant controls in the report that are not working as expected.

[NeoPa]

Nevermind...I got it to work. Thanks for the help you guys.

Oh, no worries then ;)
I sometimes have to leave a page idle for (quite) a while before I get around to posting a response.

Do you want to let us know what your solution was. In case anyone finds this thread at a later date and wonders how they should duplicate your success.

[beacon]

Ok...I lied. It partially worked, but I know it's probably because of how I have my report setup.

Let me lay it out from the beginning and see if anyone can't figure out where I'm going wrong.

I have a report that is setup like this:

(Program Header)
Program
    (SkillLevelID Header)
     SkillLevel     SkillLevelID(not visible)
           (Details)
            LName   FName   JobTitle   Email   PhoneExt
(Program Footer)
                                    Total Beginner Employees                TextBox
                                    Total Intermediate Employees           TextBox
                                    Total Advanced Employees               TextBox
                                    Total Program Employees                 TextBox
 

I'm trying to get the total individual skill level for each individual program and have it summarized in the program footer. Then at the end of the report I wanted to put a comprehensive summary.

I created a query to count the data because I couldn't use a count in the program footer to summarize the data in different sections.

When I setup my query I put CountOfSkillLev el and SkillLevel, but that just did totals for the skill level. Then I added program, but my DLookup didn't have anything in it to determine that program was necessary for the lookup.

Here's the SQL for my query:

SELECT qryWFSummary.Program, Count(qryWFSummary.SkillLevel) AS CountOfSkillLevel, tblSkillLevel.SkillLevel
FROM qryWFSummary INNER JOIN tblSkillLevel ON qryWFSummary.SkillLevelID=tblSkillLevel.SkillLevelID
GROUP BY qryWFSummary.Program, tblSkillLevel.SkillLevel;
 

Right now my DLookup looks like this:

=DLookUp("CountOfSkillLevel","qrySkillsWF","SkillLevel = 'Beginner'")
 

Thanks for the help and sorry for thinking I had it figured out.

[beacon]

Can I use an '&' to tell my DLookup that I need to sort the values by the program they describe? Is that possible?

[nico5038]

You should be able to count in a report footer without the additional query by using in the textbox:

=Count([skilllevel])
 

Nic;o)