Problem with Filtering records with DateDiff function??

[SrkrReds]

Hi:

I have a table where there is a date field and I need to filter records (for how many passed within a given amt of time. if user wants to check past 30 days, they enter 30 when prompted.). I have a parameter query as below.

SELECT [tbl Work Order].[Entry Date], [tbl Work Order].STATUS, [tbl Work Order].[Pass/Fail], DateDiff("d",[Entry Date],Now()) AS TimeFrame
FROM [tbl Work Order]
WHERE ((([tbl Work Order].STATUS) In ("submit","closed")) AND (([tbl Work Order].[Pass/Fail])="Passed") AND ((DateDiff("d",[Entry Date],Now()))<=[Enter NumDays]) AND (([tbl Work Order].[Order Type])="verify"))
ORDER BY DateDiff("d",[Entry Date],Now());

When this is run, user is prompted to enter number of days. And if they enter 30, it doesnt just stop records with date diff <= 30, but also brings in where the difference is over that. PLEASE SOME ONE HELP.

Also tried something like below: (This doesnt work the way it should either)

SELECT [tbl Work Order].PTR_NO, [tbl Work Order].[Work Order No], [tbl Work Order].[Order Type], [tbl Work Order].STATUS, [tbl Work Order].[Pass/Fail], Format([Entry Date],"Short Date") AS SubmitDate, DateDiff("d",[Entry Date],Now()) AS TimeFrame
FROM [tbl Work Order]
WHERE ((([tbl Work Order].[Order Type])="verify") AND (([tbl Work Order].STATUS) In ("submit","closed")) AND (([tbl Work Order].[Pass/Fail])="Passed") AND ((Format([Entry Date],"Short Date")) Between CDate([Enter Date:::]) And CDate(Now())));

I would like to filter records in the "Passed" state and that were submitted between today and some past date that user will enter. This filtering is weird, if I enter 3/1/05 it generates about 65 records bringing everything between 3/1/05 and today. If I enter 3/3/05, it just filters about 21 records (doesnt display any records with dates such as 3/10, 3/11 so on and 3/22, 3/38).

I am really stuck here. If anyone has an idea what I am doing wrong and suggest some tips, it would be sooooooo helpful.
Thanx in advance.

 

[PHV]

You may try this:
SELECT [Entry Date], STATUS, [Pass/Fail], CLng(Now()-[Entry Date]) AS TimeFrame
FROM [tbl Work Order]
WHERE STATUS) In ("submit","closed") AND [Pass/Fail]="Passed" AND CLng(Now()-[Entry Date])<=[Enter NumDays] AND [Order Type]="verify"
ORDER BY CLng(Now()-[Entry Date]);


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

 

[SrkrReds]

Thanx for the quick response PHV. I ran that sql and get the following error when i run it:


The expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to variables

 

[earthandfire]

The only problem that I can see with the query is if you copied and pasted it you would have picked up the extra closing bracket after STATUS in the WHERE clause.

Other than that it seems to work ok.

 

[PHV]

Good catch earthandfire.
SELECT [Entry Date], STATUS, [Pass/Fail], CLng(Now()-[Entry Date]) AS TimeFrame
FROM [tbl Work Order]
WHERE STATUS In ("submit","closed") AND [Pass/Fail]="Passed" AND CLng(Now()-[Entry Date])<=[Enter NumDays] AND [Order Type]="verify"
ORDER BY CLng(Now()-[Entry Date]);


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

 

[SrkrReds]

Sry for not mentioning in my previous post, I did correct the extra closing bracket and ran the sql and got the error. I ran the sql again now(just to see) and i still get the same error

THanx