473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Lookup Value loop

bhcob1
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
3 2154
Rabbit
12,516 Expert Mod 8TB
Suscribing. At first glance this looks more involved than I have time for right now.
Feb 16 '07 #2
Rabbit
12,516 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Julius Mong | last post by:
Hi all, I'm doing this: // Test char code wchar_t lookup = {0x8364, 0x5543, 0x3432, 0xabcd, 0xef01}; for (int x=0; x<5; x++) { wchar_t * string = (wchar_t*) malloc(sizeof(wchar_t)); string =...
2
by: CoreyWhite | last post by:
The future of computer architecture will use lookup tables. Currently computer processor speed outweighs the benefits of using computer memory for lookup tables, except in some cases. As computer...
8
by: Lucas Lemmens | last post by:
Dear pythonians, I've been reading/thinking about the famous function call speedup trick where you use a function in the local context to represent a "remoter" function to speed up the 'function...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
4
by: jon f kaminsky | last post by:
Hi- I've seen this problem discussed a jillion times but I cannot seem to implement any advice that makes it work. I am porting a large project from VB6 to .NET. The issue is using the combo box...
2
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What...
3
by: larry | last post by:
Ok I am re-coding our apps in PHP and am looking for ways to make parts easily updateable, One of the challenges in my field (non-rpofit) are various lookup tables (for incomes etc). An example...
11
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in...
2
by: anson | last post by:
this code is an example code of TPOP, i type them and can be compiled... but when give some input , it getting segment fault .... i observed that when the build() initial the word table ... it...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.