By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

Lookup Value loop

bhcob1
P: 19
Hi guys,

This is the situation.
I have 3 tables, with the following relevent fields

tblSubFile (Substantiation Files)
[ID] - autonumber
[Reference Number] - Primary Key
[Issue] - Primary Key
[Relevant NQO]

tblReqAndSubFile (Shows relationships between Substantiation Files and Requirements)
[Requirement ID] - Primary Key
[Reference Number] - Primary Key
[Issue] - Primary Key

tblReq (Requirements)
[Requirement ID] - Primary Key
[Relevant NQO for required substantiation]

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
  1.   ' Finds the Requirement for the current Substantiation File on the form
  2.         var1 = DLookup("[Requirement ID]", "tblReqAndSubFile", "[Reference Number] = Forms![frmSubFile]![Reference Number]")
  3.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  4.         var2 = DLookup("[Relevant NQO for required substantiation]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  5.         Me.Relevant_NQO = var2
  6.  
If sample data that i have would help, let me know.

Thanks
Feb 16 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Suscribing. At first glance this looks more involved than I have time for right now.
Feb 16 '07 #2

Rabbit
Expert Mod 10K+
P: 12,366
Hi guys,

This is the situation.
I have 3 tables, with the following relevent fields

tblSubFile (Substantiation Files)
[ID] - autonumber
[Reference Number] - Primary Key
[Issue] - Primary Key
[Relevant NQO]

tblReqAndSubFile (Shows relationships between Substantiation Files and Requirements)
[Requirement ID] - Primary Key
[Reference Number] - Primary Key
[Issue] - Primary Key

tblReq (Requirements)
[Requirement ID] - Primary Key
[Relevant NQO for required substantiation]

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
  1.   ' Finds the Requirement for the current Substantiation File on the form
  2.         var1 = DLookup("[Requirement ID]", "tblReqAndSubFile", "[Reference Number] = Forms![frmSubFile]![Reference Number]")
  3.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  4.         var2 = DLookup("[Relevant NQO for required substantiation]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  5.         Me.Relevant_NQO = var2
  6.  
If sample data that i have would help, let me know.

Thanks
Off topic but it seems to me as if your [Requirement ID] could just replace your [ID] from TblSubFile and you'll drop down to 2 tables.

In response to your main question:
Expand|Select|Wrap|Line Numbers
  1. var2 = DLookup("[Relevant NQO for required substantiation]", "tblReq", "[Requirement ID]='" & var1 & "' AND [Relevant NQO for required substantiation] Is Not Null")
I believe that's the correct syntax, if not then use Not IsNull([Relevant NQO for required substantiation]) instead.
Feb 16 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.