calculated date field problem w/ user defined-parameter

[keluther]

Hi, All,

I'm working on a database that is tracking youth who are participating in a drug prevention program. In one of my queries, I have a calculated date field called six_open. Six_open is equal to the baseline date from one of my tables + 166 days.

When I run the query, it is pulling the records I want. However, when I go to filter out records so I only get six_open within a specific range, I get different results depending on if I use a specific end date in the criteria field of the query (e.g. <=#12/31/04#) or if I ask the user to input the end date (e.g. <=[Enter End Date]) and then the user enters '12/31/04'.

The first query returns a correct 38 records, the second query returns 14 records, where the date doesn't even match up with the user-entered paramter. The two sets of SQL code are below. Thanks in advance for any pointers.

-Kris

SELECT DISTINCTROW qry_Interview.part_ID, ([qry_Interview]![baseline]+166) AS six_open
FROM qry_Interview
WHERE (((([qry_Interview]![baseline]+166))<=#12/31/2004#))
ORDER BY ([qry_Interview]![baseline]+166);

SELECT DISTINCTROW qry_Interview.part_ID, ([qry_Interview]![baseline]+166) AS six_open
FROM qry_Interview
WHERE (((([qry_Interview]![baseline]+166))<=[Enter End Date]))
ORDER BY ([qry_Interview]![baseline]+166);

 

[PHV]

And this ?
PARAMETERS [Enter End Date] DateTime;
SELECT DISTINCT part_ID, (baseline+166) AS six_open
FROM qry_Interview
WHERE (baseline+166)<=[Enter End Date]
ORDER BY 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[keluther]

PH,

Many thanks! That did the trick. You made my day-- this has had me scratching my head for hours. Thanks so much again.

-Kris