By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Derived field in criteria

P: 21
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].BaseProjectNumber, t_Projects.Project_Number, [t_CTP Project Data].ProjectName, [t_CTP Project Data].DurationDays, t_ProjectArea.ID_WorkType, [t_CTP Project Data].NtpDate, t_ProjectArea.ID_Facility, t_ProjectArea.ID_DatePart, t_Projects.ID_Projects, [NtpDate]+[DurationDays] AS endDate FROM ([t_CTP Project Data] RIGHT JOIN t_Projects ON [t_CTP Project Data].BaseProjectNumber = t_Projects.PIN) INNER JOIN t_ProjectArea ON t_Projects.ID_Projects = t_ProjectArea.ID_Project where [Forms]![f_projectSearch]![tbFilterEndDate] <= [t_CTP Project Data].NTPdate and [Forms]![f_projectSearch]![tbFilterStartDate] >= endDate;
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
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].BaseProjectNumber, t_Projects.Project_Number, [t_CTP Project Data].ProjectName, [t_CTP Project Data].DurationDays, t_ProjectArea.ID_WorkType, [t_CTP Project Data].NtpDate, t_ProjectArea.ID_Facility, t_ProjectArea.ID_DatePart, t_Projects.ID_Projects, [NtpDate]+[DurationDays] AS endDate FROM ([t_CTP Project Data] RIGHT JOIN t_Projects ON [t_CTP Project Data].BaseProjectNumber = t_Projects.PIN) INNER JOIN t_ProjectArea ON t_Projects.ID_Projects = t_ProjectArea.ID_Project where [Forms]![f_projectSearch]![tbFilterEndDate] <= [t_CTP Project Data].NTPdate and [Forms]![f_projectSearch]![tbFilterStartDate] >= 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]![tbFilterStartDate] >= endDate;

TO:

[Forms]![f_projectSearch]![tbFilterStartDate] >= [NtpDate]+[DurationDays];
Jan 8 '08 #2

P: 21
DOH! Thanks for the assistance!
Jan 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.