Parameter Query Problem

[David McLean]

Hi There

I've got a problem with a parameter query that I'm trying to run. I'd like
to pull out records based on a date value that is calculated (7 days before
the date store in tblReports). In the criteria section of my query under the
calculated date field, I've got "Between [From what date (dd-mmm-yy):] And
[To what date (dd-mmm-yy):]" without the quote marks.

Whats happening is that I'm getting results that are outside of the
parameters. For example, when i try to query records that are between 12 Jun
09 and 20 Jun 09, I'm getting a few records from Jul 09 in my results.

here is my SQL view if that helps:

SELECT DISTINCT tblCentres.CentreLocation, tblClients.CaseNumber,
tblClients.FirstName, tblClients.LastName, [DueToCM]-7 AS Expr1
FROM (tblCentres INNER JOIN tblClients ON tblCentres.CentreID =
tblClients.CenterID) INNER JOIN tblReports ON tblClients.ClientID =
tblReports.ClientID
WHERE ((([DueToCM]-7) Between [From what date (dd-mmm-yy):] And [To what
date (dd-mmm-yy):]))
ORDER BY [DueToCM]-7;

If anyone has any ideas, or would like to know more information, please let
me know. I'd appreciate any help I can get.

David

 

[KARL DEWEY]

Try this --
WHERE ((([DueToCM]-7) Between CVDate([From what date (dd-mmm-yy):]) And
CVDate([To what date (dd-mmm-yy):])))

 

[John Spencer MVP]

See International Dates in Access at:
http://allenbrowne.com/ser-36.html

That should give you an idea of what is going on and solutions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County