Hi all, I have created a query which pulls together a Project meeting agenda (for a report), and I'm trying to set the Risk Status to "02 At Risk", where the overall project status isn't in number of statuses, but where the change_target date is les than or equal to today's date.

The query currently looks like this:



The error I'm getting is: "Syntax error (comma) in query expression...".

Any ideas?

Hmmm, if I make a calculated field called change_risk_status2, it works, however it's obviously not setting the change_risk_status to the new "02 At Risk" status for all applicable records in the table... just the calculated field.

How do I get the original change_risk_status value updated?

You have a circular reference. The alias you're using "change_risk_status", is the same as one of your fields that you have in your expression. Try changing the name of the alias, that should resolve the problem.

Actually, you should be able to remove the circular reference by qualifying the reference to the field name with the table name:

change_risk_status: Iif([change_target_date]<=Date() And ([change_status] Not In ("03 CRM Approved to Develop","04 Waiting to be Assigned","05 Development in Progress","06 Testing in Progress","07 Ready to Implement","08 CRM Approved to Implement")),[NameOfTable].[change_risk_status],"02 At Risk")

As a preliminary suggestion, if the list of values to check for the "Not In" statement are the field [StatusCode] in a table (tblCodes) you could have:

Not In (SELECT [StatusCode] FROM tblCodes)

In this way you could add to or revise the values without having to hard code the expression.

One thing I notice is that you are setting the value of a calculated field [change_risk_status] to itself if the criteria are met. If this works at all, I don't see how it will produce a meaningful result.

Are change_risk_status and change_status supposed to be the same field?

To update you would need an update query, but I am not entirely clear on the update you wish to perform. You mention "all applicable records", but I don't see what designates an applicable record.