Chart Produced by Query with User Supplied Criteria 1

[Elvis72]

I have this query:

SELECT TblProjectInformation.JobProposalNo, TblConstructionServices.Title, Count(TblProjectInformation.ProjectID) AS CountOfProjectID, TblDate.TheDate, TblProjectInformation.[HR Classification], Count(TblProjectInformation.[HR Classification]) AS [CountOfHR Classification]
FROM TblDate, TblConstructionServices INNER JOIN TblProjectInformation ON TblConstructionServices.JobProposalNo = TblProjectInformation.JobProposalNo
WHERE (((TblDate.TheDate) Between #1/1/2004# And #1/1/2025#) AND ((TblProjectInformation.[Mobilization Date])<=[tblDate].[TheDate]) AND ((TblProjectInformation.[Demobilization Date])>=[tblDate].[TheDate]))
GROUP BY TblProjectInformation.JobProposalNo, TblConstructionServices.Title, TblDate.TheDate, TblProjectInformation.[HR Classification]
HAVING (((TblProjectInformation.JobProposalNo)=[Enter Job/Proposal Number]));

Which when I try and run my chart I end up with:

The Microsoft Jet database engine does not recognize [Enter Job/Proposa Number] as a valid field name or expression.

I have found a couple of postings about this error, but I don't seem to be able to follow them?

thanks so much!~

 

[genomon]

The HAVING clause will only work for field names in the table. Have you tried building a parameter query in QBE with the wizard? Also have a look at "Create a Parameter Query" in Access help.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[lespaul]

additionally Access uses the HAVING clause incorrectly...you can move that to a WHERE clause and it should work:

SELECT TblProjectInformation.JobProposalNo, TblConstructionServices.Title, Count(TblProjectInformation.ProjectID) AS CountOfProjectID, TblDate.TheDate, TblProjectInformation.[HR Classification], Count(TblProjectInformation.[HR Classification]) AS [CountOfHR Classification]
FROM TblDate, TblConstructionServices INNER JOIN TblProjectInformation ON TblConstructionServices.JobProposalNo = TblProjectInformation.JobProposalNo
WHERE (((TblDate.TheDate) Between #1/1/2004# And #1/1/2025#) AND ((TblProjectInformation.[Mobilization Date])<=[tblDate].[TheDate]) AND ((TblProjectInformation.[Demobilization Date])>=[tblDate].[TheDate])) [b]AND (((TblProjectInformation.JobProposalNo)=[Enter Job/Proposal Number]))[/b]
GROUP BY TblProjectInformation.JobProposalNo, TblConstructionServices.Title, TblDate.TheDate, TblProjectInformation.[HR Classification];

Leslie

Have you met Hardy Heron?

 

[Elvis72]

That works GREAT LesPaul!~

Thanks so much for your help!~