Crosstab queries and SQL problems

[Guest]

OK. I have a crosstab query that needs to show the project name across the
top, people's names down the side, and the time on each project in the
middle. Along the side next to the person's name, I have the total time spent
on all projects for each person.

My problem is this: the query has some constraints on it - the projects and
times will only be pulled if the time stamp is with a range determined from
fields on a form, and I want to display only the project selected from a
combo box on that same form.

So I have the basic query set up like this in SQL view:

TRANSFORM Sum([ProgramSummary2-b].Time_Spent_On_Project) AS
SumOfTime_Spent_On_Project
SELECT [ProgramSummary2-b].Employee_Name,
Sum([ProgramSummary2-b].Time_Spent_On_Project) AS [Total Of
Time_Spent_On_Project]
FROM [ProgramSummary2-b]
GROUP BY [ProgramSummary2-b].Employee_Name
PIVOT [ProgramSummary2-b].Program_Name;


This brings up the whole thing - all projects, with totals from the
beginning of time to the end of time.

We have a VB back end for this - when we click the "Go" button on the form,
it runs a series of commands. Most notably, it defines a string called
strSQL. We set the QueryDef.SQL property to be strSQL before we run
DoCmd.OpenQuery(Crosstab_Query).

So my code looks like this:

strSQL =
"TRANSFORM Sum(ProgramSummary2-b.Time_Spent_On_Project) AS
SumOfTime_Spent_On_Project
SELECT ProgramSummary2-b.Employee_Name,
Sum(ProgramSummary2-b.Time_Spent_On_Project) AS [Total Of
Time_Spent_On_Project]
FROM ProgramSummary2-b
WHERE " & ministr & "
GROUP BY ProgramSummary2-b.Employee_Name
PIVOT ProgramSummary2-b.Program_Name;"


This does the same thing, but ministr (the Where criteria) is just putting
the dates and box value from the form into an SQL statement.

ministr = "(((ProgramSummary2-b.Date_When_Worked_On_Project) >#" & Date1 &
"# And (ProgramSummary2-b.Date_When_Worked_On_Project) <#" & Date2 & "# And
(ProgramSummary2-b.Program_Name = Combo17.Value)"

The problem comes in here - the query doesn't recognize the Where statement
in the QueryDef.SQL property. So I get the same response no matter which
dates I choose, or which project I choose. All projects, from beginning of
time to end of time.

Any help?

~Yblitzka

 

[Duane Hookom]

Did you try open your saved query to view the SQL/design after setting the
SQL property?

Also, your query can't resolve Combo17.value when part of the SQL property.
Try:

ministr = "ProgramSummary2-b.Date_When_Worked_On_Project >#" & Date1 &
"# And ProgramSummary2-b.Date_When_Worked_On_Project <#" & Date2 & "# And
ProgramSummary2-b.Program_Name = """ & Combo17.Value & """;"

This assume Program_Name is text.