Derived field in criteria

[sconard]

I cannot seem to refer to a derived field in criteria. Better phrased: I cannot refer to a derived field in the where clause of the query. The following does not use the value from the derived field and causes access to pop a message box to fill the value for endDate. I have tried the name of the query [q_ProjectSearch].endDate and this also fails. I have tried quotes "endDate" and this also fails. I have also tried: [q_ProjectSearch]![endDate], [endDate], endDate. I am running the query only and not using the form it is intended for to remove those elements.

Thank you for any assistance

SELECT [t_CTP Project Data].BaseProjectNum ber, t_Projects.Proj ect_Number, [t_CTP Project Data].ProjectName, [t_CTP Project Data].DurationDays, t_ProjectArea.I D_WorkType, [t_CTP Project Data].NtpDate, t_ProjectArea.I D_Facility, t_ProjectArea.I D_DatePart, t_Projects.ID_P rojects, [NtpDate]+[DurationDays] AS endDate FROM ([t_CTP Project Data] RIGHT JOIN t_Projects ON [t_CTP Project Data].BaseProjectNum ber = t_Projects.PIN) INNER JOIN t_ProjectArea ON t_Projects.ID_P rojects = t_ProjectArea.I D_Project where [Forms]![f_projectSearch]![tbFilterEndDate] <= [t_CTP Project Data].NTPdate and [Forms]![f_projectSearch]![tbFilterStartDa te] >= endDate;

[puppydogbuddy]

I cannot seem to refer to a derived field in criteria. Better phrased: I cannot refer to a derived field in the where clause of the query. The following does not use the value from the derived field and causes access to pop a message box to fill the value for endDate. I have tried the name of the query [q_ProjectSearch].endDate and this also fails. I have tried quotes "endDate" and this also fails. I have also tried: [q_ProjectSearch]![endDate], [endDate], endDate. I am running the query only and not using the form it is intended for to remove those elements.

Thank you for any assistance

SELECT [t_CTP Project Data].BaseProjectNum ber, t_Projects.Proj ect_Number, [t_CTP Project Data].ProjectName, [t_CTP Project Data].DurationDays, t_ProjectArea.I D_WorkType, [t_CTP Project Data].NtpDate, t_ProjectArea.I D_Facility, t_ProjectArea.I D_DatePart, t_Projects.ID_P rojects, [NtpDate]+[DurationDays] AS endDate FROM ([t_CTP Project Data] RIGHT JOIN t_Projects ON [t_CTP Project Data].BaseProjectNum ber = t_Projects.PIN) INNER JOIN t_ProjectArea ON t_Projects.ID_P rojects = t_ProjectArea.I D_Project where [Forms]![f_projectSearch]![tbFilterEndDate] <= [t_CTP Project Data].NTPdate and [Forms]![f_projectSearch]![tbFilterStartDa te] >= endDate;

I believe you cannot use a column alias in a where clause, only in an Order By clause; so change this:

[Forms]![f_projectSearch]![tbFilterStartDa te] >= endDate;

TO:

[Forms]![f_projectSearch]![tbFilterStartDa te] >= [NtpDate]+[DurationDays];

[sconard]

DOH! Thanks for the assistance!