Lookup value based on 2 fields

DKM

Registered User.
Local time
Today, 07:45
Joined
Feb 24, 2007
Messages
24
I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.

I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".

Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.

So the TblLevels looks like this:

Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3

So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"

any ideas on how i can do this one?
 
Here is an example of a DLOOKUP that uses two filters:

TotalDispensed = DLookup("[TotalDispensed]", "RxHistory", "[PxPracNo] = " & PxPracNo.Value & " AND [Lastvisit] = #" & Me.Lastvisit & "#")

Just be careful withy the "" and ' syntax
 
Ted

Many thanks for the reply. If ive got this right the "[TotalDispensed]" relates to the field on the table i want to look up, the "RxHistory" is the name of the table and the "[PxPracNo] is the field on the query that im using as my criteria. However im slightly unsure of what the second part of the it relates to within the context.

Knowing me i missed something from the intial post (eyes were going screwy from searching by that time). On the query "qrymainform" are the 2 fields that i need to use "points" and "familyname". the points are automatically generated, the Familyname is set by the user (so could be changed to anyone of 3 by the user). So if a person scores 200 points and the admin Family is selected the lookup needs to return level 3, if they score 200 and the operational family is selected i need it to return level 2.

if the reply does this could someone briefly explain how it links together to get the value from the relevant field on the "TblLevels" table.

Much appreciated
 
Maybe something like this then

strAdmin = DLookup("[Admin]", "QryMainFrm", "[Points] = " & intPoints &" AND [FamilyName] = ' " & strFamilyName & " ' ")
 
almost there

Hi Ted

Have been trying to work on the on the first option (i have changed the layout of the table to try and simplify it a bit). Playing with the criteria above i have it running nearly as it should except it is still producing an error linked to the syntax. it will bring up a box saying that the 2 fields im using as my criteria are being picked up, but the lookup isnt working.

Are there any specific rules to follow regarding the placement of the " and ' within a lookup?

I have attached a copy of the db so you can see what im trying, and the errors etc (its easier than explaining bit by bit :) ). Im working off QryMainForm for the location of the lookup. It is looking up data from QryJobFamilyLink.FamilyLevel field.
 

Attachments

I hadn't appreciated that you were using the DLOOKUP in a query; I assumed it was VB code in a form. Syntax is therefore different to that I gave you. Please try this.

Level: DLookUp("[FamilyLevel]","QryJobFamilyLink","[Score] = " & [Points]) And DLookUp("[FamilyLevel]","QryJobFamilyLink","[ID] = " & [JobFamily_ID])
 
hmmm this is now confusing me. I have tried working with the Dlookup one above, it appears to work ok (it doesnt return an error) however it only gives me a value of -1 in the level field. I have tried switching some of the names and details but still not getting it to work. Dlookup isnt going to need anything specific set up (such as librarys etc) does it?

Am having the same problem working on both Vista and windows NT systems. Did this work in the example DB attached?
 
No libraries needed and it is not operating system specific. I would suggest you have a logic error somewhere. What should the value be in the Level field; can you reply with an answer to a record? Bit busy today but will try and look at it later.
 
Thought id cover all angles.

The lookup is using a query "qryJobfamilylink" with the following fields:

Score (Number)
JobFamily_ID (between 1 and 7/ name of family)
FamilyLevel (Text)

So the query looks like this

180 - 1/Operational - Level 1
185 - 1/Operational - Level 2
185 - 2/Admin - Level 1
190 - 1/Operational - Level 3
190 - 2/Admin - Level 2
190 - 3/Technical - Level 1

So i have a score of 185, and have the admin job family selected i need it to return "Level 1". whereas if the score is 185 and i have the Operational family selected i need it to return "Level 2".

the help is much appreciated on it Ted.
 
This appears to work in your sample:

Level: DLookUp("[FamilyLevel]","QryJobFamilyLink","[Score] = " & [Points] & " AND [JobFamily] = " & [JobFamily_ID])
 

Users who are viewing this thread

Back
Top Bottom