I am looking at having 3 fields driving the change.
Yup, I think you should concentrate on getting some sort of result considering only one field.
You are correct, ...AND [Sub_No]=" & [Sub_No]-1 is looking to the previous.
MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True And "CR_No=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) gives me a constant -1 output.
MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) = no output blank/null
You have a couple different issues with each of your attempts. I suggest you simplify things and retrieve the value of Action_Complete from the Change Request table WHERE Sub_No = the current Sub_No plus one.
So something like this ...
Code:
MyAlias: DLookup("[Action_Complete]", "[Change Request]", "[Sub_No]=" & [Sub_No] + 1)
If you can get that to work, where you actually get a value from one of the records within table, then you can add on to it. I see the next step as adding a key value to your criteria. This is why I was suggesting something like
Code:
"[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1"
But, adding another field to the criteria is not an easy thing to do. The syntax has to be perfect. Also, we may not be able to use the Change Request table. We may need to create another query that provides the appropriate subset and replace Change Request with the name of a query.