Avatar of fernandoweb
fernandoweb
 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]![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

Open in new window

SQL

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
peter57r

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
fernandoweb

ASKER
That was simple!  Thanks very much.
Jim Dettman (EE MVE)

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.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy