Manipulating Calculated Fields

[Wrinkles]

I developed a query that calulates when a contract is reviewed i.e 5 weeks and 9 weeks after the commencement date. These are called CDE1 and CDE2. This works well and the CDE1 and CDE2 dates are displayed ok.

The query above is:
SELECT Contract.ContractNo, Contract.APSNo, Contract.Firstname, Contract.Surname, Contract.AgencyDesc, Contract.FacilityName, Contract.Designation, Contract.WeeklyHrs, Contract.PositionNo, Contract.ComDate, Contract.EndDate, IIf(DateAdd("ww",5,[comdate])<[enddate],DateAdd("ww",5,[comdate])) AS 1stCDE, IIf(DateAdd("ww",9,[comdate])<[enddate],DateAdd("ww",9,[comdate])) AS 2ndCDE
FROM Contract;

The problem is that I now want to access contracts under these CDE dates between two dates that is user defined (startdate) and (enddate).

As the above are calculated fields, how can I develop a new query based on the above as CDE1 and CDE2 are already calculated fields?

 

[The_Doc_Man]

A query can be a recordsource for another query just like a table can be a recordsource for a query. From the "outer" query, the calculated fields look like "variant" fields, usually, unless you used CDate, CInt, CSng, CDbl, etc. to force a particular format to be returned through the "inner" query.

 

[Wrinkles]

Thanks, have'nt used Access for sometime and have forgotten parts of it. I have used the query as above as sourcedata and tried to convert the date calculation from a variant to date using CDate but in the second query it only recognises literal dates entered as a range rather than a range entered through parameters. I must be missing something.

I did the following:
Converted part of the original query to return a date using CDate
1stCDE: IIf(DateAdd("ww",5,[comdate])<[enddate],CDate(DateAdd("ww",5,[comdate])))

Then using the above as the sourcedata for the second query, I used in the criteria of the field "Between [StartDate] and [Enddate]" but it returns all records. If I enter literal dates "Between #01/05/2005# and #31/05/2005#" works ok.

I assume the conversion to CDate should be in the original query not the calling query.

Thanks for your initial reply.