The relationship orignally between tblSubFile and tblReq was many-to-many, this is the reason the bridge talbe tblReqAndSubFile was created.

What is required, is that the field [Relevant NQO] in tblSubFile is being filled out by finding the value of [Relevant NQO for required substantiation] in tblReq. As a Substantiation File can be related to several Requirements, it only uses the first Requirement.

The problem is, I want the loopup to skip to the next related Requirement if the [Relevant NQO for required substantiation] was blank, and keep going until it finds a non-null entry in [Relevant NQO for required substantiation], and then use this value. Or if all of the [Relevant NQO for required substantiation] fields for the related Requirements are null, return a null value.

Currently the code i use is as follows

Expand|Select|Wrap|Line Numbers

' Finds the Requirement for the current Substantiation File on the form

The relationship orignally between tblSubFile and tblReq was many-to-many, this is the reason the bridge talbe tblReqAndSubFile was created.

What is required, is that the field [Relevant NQO] in tblSubFile is being filled out by finding the value of [Relevant NQO for required substantiation] in tblReq. As a Substantiation File can be related to several Requirements, it only uses the first Requirement.

The problem is, I want the loopup to skip to the next related Requirement if the [Relevant NQO for required substantiation] was blank, and keep going until it finds a non-null entry in [Relevant NQO for required substantiation], and then use this value. Or if all of the [Relevant NQO for required substantiation] fields for the related Requirements are null, return a null value.

Currently the code i use is as follows

Expand|Select|Wrap|Line Numbers

' Finds the Requirement for the current Substantiation File on the form

Domain Aggregate functions are a grey area for me where it comes to using SQL syntax. Certainly the X Is Null construct is SQL specific. In VBA generally the syntax is IsNull(). I expect the Domain Aggregate functions will actually handle the SQL syntax fine.