query criteria in access 2007

[jeskit]

Hi

I wanted to know if anyone could tell me if it is possible to create a query where the criteria has a where clause?

I want to add criteria to a query i am writing which looks for any row which is <= the date column of table 3, however when the criteria looks at table 3 for the date, i only want it to look at the row with 1 in the Id column.

So basically i need the criteria to do something like <= table3.date where table3.id = 1

Is this possible? If not is there any way of doing it?

Thank you



jeskit

[hertfordkc]

queries, with one based on the other. If you need the SQL, go to the SQL view and copy it.
In other words, construct a query which returns only records where the ID = 1. Then base the other query on that one.

[jeskit]

hi,

not really what i have is the following query:

SELECT Post.AppRefID, Post.Item, Post.DateRqd, Post.DateChased, Post.DateRcd, AppChecklist.FirstName, AppChecklist.AppName, AppChecklist.AppRcd, AppChecklist.IFA_FSA

FROM ChasingReport, AppChecklist INNER JOIN Post ON AppChecklist.AppRefID = Post.AppRefID

WHERE (((Post.Item) Like "*await referral*") AND ((IsNull([AppChecklist].[StartDate]))=True) AND ((([Post].[DateChased])+21)<=[ChasingReport]![Date]) AND ((IsNull([Post].[DateRcd]))=True) AND ((IsNull([AppChecklist].[AppClosed]))=True)) OR (((Post.Item) Like "*await referal*") AND ((IsNull([AppChecklist].[StartDate]))=True) AND ((([Post].[DateChased])+21)<=[ChasingReport]![Date]) AND ((IsNull([Post].[DateRcd]))=True) AND ((IsNull([AppChecklist].[AppClosed]))=True)) OR (((Post.Item) Like "*HT/Wt*") AND ((IsNull([AppChecklist].[StartDate]))=True) AND ((([Post].[DateChased])+21)<=[ChasingReport]![Date]) AND ((IsNull([Post].[DateRcd]))=True) AND ((IsNull([AppChecklist].[AppClosed]))=True));

However i need to chnage this part of the criteria ((([Post].[DateChased])+21)<=[ChasingReport]![Date]) so instead of just <=[ChasingReport]![Date] it only looks at the date in ChasingReports table where the column Id in Chasing Reports is 1

Does this help? I am sorry i am not good at explaining things in a way otehrs would understand