Enter parameter value RAG error

[aman]

Hi Guys

I am writing the following query and it gives me above error message at RAG. Any help will be much appreciated.

SELECT qry_Model_AnnualTest.StaffName, qry_Model_AnnualTest.AnnualTest, qry_Model_AnnualTest.Date_Completed, IIf([RAG]=1,"Red",IIf([RAG]=2,"Amber","Green")) AS Priority
FROM qry_Model_AnnualTest
WHERE (((qry_Model_AnnualTest.DateEnd) Is Null));

 

[Minty]

It can't find [RAG] as a field anywhere. So where is it supposed to be coming from?

 

[aman]

RAG is an expression. I am writing the following query but want to filter by RAG rating 3. I tried to put criteria 3 under RAG and it didn't work:

SELECT qry_Model_AnnualTest.StaffName, qry_Model_AnnualTest.AnnualTest, (DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0)) AS Exp1, IIf([Exp1]=[Recurrence],2,IIf([Exp1]>[Recurrence],1,3)) AS RAG, qry_Model_AnnualTest.Date_Completed, IIf([RAG]=1,"Red",IIf([RAG]=2,"Amber","Green")) AS Priority
FROM qry_Model_AnnualTest
WHERE (((qry_Model_AnnualTest.DateEnd) Is Null));

 

[Minty]

You generally can't refer to a calculated field within the same query by it's alias. (Although I have seen it work sometimes, it's bad practice to get used to it, as it definitely doesn't work in SQL server)

Either recreate the expression in your next calculation, or save the query and base another query of that, or use a sub query to produce the result and use the field from that.

 

[isladogs]

Using an expression in other parts of a query rarely works.
Spell out each expression explicitly if you are going to do that:

SELECT qry_Model_AnnualTest.StaffName, qry_Model_AnnualTest.AnnualTest, (DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0)) AS Exp1, IIf(Month([Date_Completed])+1,0))=[Recurrence],2,IIf(Month([Date_Completed])+1,0))>[Recurrence],1,3)) AS RAG, qry_Model_AnnualTest.Date_Completed, IIf(IIf(Month([Date_Completed])+1,0))=[Recurrence],2,IIf(Month([Date_Completed])+1,0))>[Recurrence],1,3))=1,"Red",IIf(IIf(Month([Date_Completed])+1,0))=[Recurrence],2,IIf(Month([Date_Completed])+1,0))>[Recurrence],1,3))]=2,"Amber","Green")) AS Priority
FROM qry_Model_AnnualTest
WHERE (((qry_Model_AnnualTest.DateEnd) Is Null));

Hopefully I've copied & pasted it correctly
But as you can see its now very messy.
If it works, great BUT it may be slow and could almost certainly be done better by another method.
For example, do one query to get Expr1, then another to get RAG (or have a lookup table for those values) then a final select query based on that

EDIT: this time Minty beat me to it!

 

[aman]

Thanks Ridders , it worked. I am having another issue that there are some duplicate records present for staff members with different unique Ids in the table. So I want to add something in the below query so that it shows maximum Date_completed records only and not the duplication.

Can this be done ? Please see attached the spreadsheet for more explanation. The highlighted records need to be removed .

SELECT qry_Model_AnnualTest.StaffName, qry_Model_AnnualTest.AnnualTest, IIf((DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0))=[Recurrence],2,IIf((DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0))>[Recurrence],1,3)) AS RAG, qry_Model_AnnualTest.Date_Completed, IIf([RAG]=1,"Red",IIf([RAG]=2,"Amber","Green")) AS Priority, qry_Model_AnnualTest.DeptRef, qry_Model_AnnualTest.AnnualTestID
FROM qry_Model_AnnualTest
WHERE (((IIf((DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0))=[Recurrence],2,IIf((DateSerial(Year(Date()),Month(Date())+1,0))-(DateSerial(Year([Date_Completed]),Month([Date_Completed])+1,0))>[Recurrence],1,3)))<>3) AND ((qry_Model_AnnualTest.DeptRef)=4) AND ((qry_Model_AnnualTest.DateEnd) Is Null));

 

[isladogs]

Thanks Ridders , it worked. I am having another issue that there are some duplicate records present for staff members with different unique Ids in the table. So I want to add something in the below query so that it shows maximum Date_completed records only and not the duplication.

Can this be done ? Please see attached the spreadsheet for more explanation. The highlighted records need to be removed .

You highlighted the most recent record for Christine Ponsford by mistake
You could try substituting with Max([Date_Completed]) throughout your query (not tested).
However, its getting so complex now I really suggest you break it down into separate queries as previously mentioned