Weeks Old Calculation (over 2 weeks) 1

[snyperx3]

I'm making a query to show me the IssueID of an issue that is still Active after 2 weeks of being open. Lost yet?

Here is my query so far:
SELECT Issues.ID, Issues.Status, Issues.[Opened Date], Year([Opened Date]) & IIf(Len(Format([Opened Date],"ww"))=1,"0") & Format([Opened Date],"ww") AS IssueWeek, Year(Date()) & IIf(Len(Format(Date(),"ww"))=1,"0") & Format(Date(),"ww") AS ThisWeek, [ThisWeek]-[IssueWeek] AS WeeksOld, Issues.[2weekDate]
FROM Issues
WHERE (((Issues.Status)="Active"));

This returns all the information I need to be able to calculate how many weeks old the issue is. The problem is that when i try putting in "AND (([ThisWeek]-[IssueWeek])>2)" into the where section (to get only the ones over 2 weeks old) it asks me to identify ThisWeek and IssueWeek even though i have already defined in the query! The WeeksOld field works just fine with those variables. When i put all the calculation into a single field and put >2 with it i get an overflow.

Any ideas?

Thanks for your help.

-Pete

 

[PHV]

You may try this:
WHERE Status='Active' AND Date()>[Opened Date]+14

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

 

[Golom]

Try

WHERE Status="Active" AND
(Year(Date()) & IIf(Len(Format(Date(),"ww"))=1,"0") & Format(Date(),"ww")  - 
 Year([Opened Date]) & IIf(Len(Format([Opened Date],"ww"))=1,"0") & Format([Opened Date],"ww") ) > 2

The WHERE clause is evaluated before the SELECT clause so you cannot use field aliases in WHERE.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[snyperx3]

Golom : thanks for your response, but that returned an error for me =\

PHV : thanks, that works just fine.

-Pete