asked on
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]![t
Between [Forms]![frmDateFilter]![t
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
SQL
Last Comment
Jim Dettman (EE MVE)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That was simple! Thanks very much.
One other thing; make sure you add a parameters statement to the top of the SQL statment:
PARAMETERS Forms![frmDateFilter]![txt
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.
PARAMETERS Forms![frmDateFilter]![txt
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.