Querying a formula based field

[George]

Good night,



I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.

I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.

How can this problem be rectified?

George

[pbaldy]

Switch to SQL view and change the alias to the formula. In other words

WHERE Alias = Fail

to

WHERE YourFunctionHere = Fail

[George]

Thank you for your response but I am not clear. Here is my actual query : ON Programs.[Program Code] = [Class by Course].[Program Code]) ON Courses.[Course ID] = [Class by Course].[Course ID]
WHERE (((Programs.[Prog Name])=" Child Care & Nursery Management.cert") AND ((IIf([Remarks]>40 And [Remarks]<50,"Supplimental",IIf(nz([Remarks])<41,"Repeat","")))="Repeat"));

[pbaldy]

Well, that's part of an actual query. I don't see anything that says Pass or Fail there. What exactly does the parameter prompt say?

[George]

Pardon me - I gave a simplified scenario using fail and pass. In actual fact it was "Repeat" and "Supplemental". The actual parameter prompt says: Remarks.

[pbaldy]

Is Remarks a field in the table? If it's an alias for a calculated field, the calculation needs to replace it in the WHERE clause.

[George]

Thank you. Yes, remarks is a field, but it has a very long formula. Also every time this field is changed it would need updating in the where clause and this can lead to errors especially since copy and paste does not work.

[pbaldy]

Because of the order in which the clauses are evaluated, you can't use an alias in the WHERE clause. Perhaps your simplest solution is to leave this query without the criteria, then create another query based on this one and have the criteria there. That way you can use Remarks in the criteria.

[George]

Thanks. It sounds feasible, but for me it might be challenging to make a query from a query. I would try and if I am unsuccessful I would check the forum again

[pbaldy]

Easy as pie. When it shows you the list of tables you can add, there's a tab for queries. From there out there's no real difference.

[George]

Thanks again. It worked. My first time trying something like this.

[pbaldy]

No problemo!