Combined 2 queries & now parameter problems 1

[kentwoodjean]

Have 2 queries that I have combined as a 3rd query to use for a report. What happens is if I show the date parmeter of *2005* under weekending, I get only 2005 data. If I show the {Enter Begin Date} AND [Enter End Date] I do not get any data. What would be causing this? The date is formated as a short date on all queries.


SELECT ScoreTeamWkly.Team, ScoreTeamWkly.Weekending, ScoreTeamWkly.Ext, ScoreTeamWkly.LastName, ScoreTeamWkly.FirstName, ScoreTeamWkly.[Avg TMT(Min)], [Avg TMT(Min)]-[AvgIWRpio] AS PIOtmt, ScoreTeamWkly.NCH, ScoreTeamWkly.[Att (Sec)], ScoreTeamWkly.[Avg Wrap (Sec)], ScoreTeamWkly.[No Out], ScoreTeamWkly.[Avg Out (Sec)], ScoreTeamWkly.[Avg Ins (Sec)], ScoreTeamWkly.[Avg Hold (Sec)], ScoreTeamWkly.[Avg Idle (Sec)], ScoreTeamWkly.[No Voice Msg], ScoreTeamWkly.[Avg VMsg (Sec)], ScoreTeamWkly.[Incomplete Num], ScoreTeamWkly.[Incomplete (Sec)], ScoreTeamWkly.EmployeeID, ScoreIWRcombWkly.IWRFat, ScoreIWRcombWkly.IWRnr, [LastName] & "," & [FirstName] AS NAME, ([ATT (Sec)]+[Avg Wrap (Sec)]) AS ACHT, ScoreIWRcombWkly.IWRtot, ScoreIWRcombWkly.IWRpio, ScoreIWRcombWkly.AvgIWRpio
FROM ScoreTeamWkly INNER JOIN ScoreIWRcombWkly ON (ScoreTeamWkly.Ext=ScoreIWRcombWkly.Ext) AND (ScoreTeamWkly.Weekending=ScoreIWRcombWkly.Weekending)
GROUP BY ScoreTeamWkly.Team, ScoreTeamWkly.Weekending, ScoreTeamWkly.Ext, ScoreTeamWkly.LastName, ScoreTeamWkly.FirstName, ScoreTeamWkly.[Avg TMT(Min)], ScoreTeamWkly.NCH, ScoreTeamWkly.[Att (Sec)], ScoreTeamWkly.[Avg Wrap (Sec)], ScoreTeamWkly.[No Out], ScoreTeamWkly.[Avg Out (Sec)], ScoreTeamWkly.[Avg Ins (Sec)], ScoreTeamWkly.[Avg Hold (Sec)], ScoreTeamWkly.[Avg Idle (Sec)], ScoreTeamWkly.[No Voice Msg], ScoreTeamWkly.[Avg VMsg (Sec)], ScoreTeamWkly.[Incomplete Num], ScoreTeamWkly.[Incomplete (Sec)], ScoreTeamWkly.EmployeeID, ScoreIWRcombWkly.IWRFat, ScoreIWRcombWkly.IWRnr, ScoreIWRcombWkly.IWRtot, ScoreIWRcombWkly.IWRpio, ScoreIWRcombWkly.AvgIWRpio
HAVING (((ScoreTeamWkly.Team)=[Enter Team #]) AND ((ScoreTeamWkly.Weekending)=[Enter Begin Date] And (ScoreTeamWkly.Weekending)=[Enter End Date]));

 

[Golom]

Two problems. First your HAVING clause should be a WHERE clause because it constrains fields and not aggregate results. Second, you don't have a BETWEEN clause ... rather you have set the requirement that ScoreTeamWkly.Weekending is to be equal to both [Enter Begin Date] and [Enter End Date] which can happen only if both parameters are the same.

WHERE (((ScoreTeamWkly.Team)=[Enter Team #]) AND
 ((ScoreTeamWkly.Weekending)
             BETWEEN [Enter Begin Date] 
             AND [Enter End Date]))

 

[dhookom]

Third, you should kick your application up a notch and abandon parameter prompts. Use controls on forms for your criteria.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

As alwsys, great advice Duane. Can't believe that I forgot the "Between". For as long as I played with it I didn't even notice, and that was one of the very first things that I learned. That is why I needed someone else to look at it. Reason I did not use the WHERE as sugested is that I wanted it to appear on the header of the report. It all works perfectly now as a result of a very stupid oversight on my part.

It is my goal in 2006 to review all the databases, those I inherited (such as this one) and have had to band aid along the way and those that I put together. I have learned so much over the last year that it is time to streamline all of them.

Thanks so much for your help.

 

[dhookom]

Actually, Golom found the "Between" error in your SQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]