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

ASKER CERTIFIED SOLUTION

peter57r

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.