How do I get parameters into a query which can't be viewed in Design view?
I've got a query which I can only see in SQL view as Access can't represent the join expression in design view. I want to add to it a date parameter so it only includes results within two dates which the user types into a form called frmDateFilter, but I'm not sure of the syntax to get it into the code since I normally just add the parameter text below into the design view of a query:
Between [Forms]![frmDateFilter]![txtStart] And [Forms]![frmDateFilter]![txtEnd]
SELECT count(tblClientDetails.clientID)FROM ( (tblClientDetails INNER JOIN tblEpisodeDetails ON tblClientDetails.clientID = tblEpisodeDetails.ClientID) INNER JOIN tblAudit as Audit1 ON tblEpisodeDetails.episodeID = Audit1.EpisodeID) INNER JOIN tblAudit as Audit2 on tblEpisodeDetails.episodeID = Audit2.EpisodeID and Audit1.[Date] < audit2.[Date]WHERE Audit1.TotalScore > Audit2.TotalScore
One other thing; make sure you add a parameters statement to the top of the SQL statment:
PARAMETERS Forms![frmDateFilter]![txtStart] as DateTime, Forms![frmDateFilter]![txtEnd] as Datetime;
SELECT ....
In query designer, you can set this through view/parameters.
The reason for this is that Access will have to guess the data type if not explicitly stated. In some cases, it will guess incorrectly, especially with DateTime fields as they are actually stored as a double float.
JimD.