DLookUp Function

[bnageshrao]

I have not used Dlookup function and I went thru the expression bulider to use it while updating a table using query. I have the following situation as an example.

I have a table called Leads and it has two of the columns are called Lead Name and AM Name

Lead Name AM Manager
1. InValid Invalid
2. Invalid (Blank)
3. Rick Hu Thomas King
4. (Blank) John Reid

I have to update the Lead Name Column and the criteria is if the Lead Name has a value of Invalid or if it is Blank, then I have to replace those values with the first AM Manager name value. If the first AM manager name is either invaild or Blank, then I have to choose the next AM manager's value. So what I want to achieve in those columns after the update is as follows.

Lead Name AM Manager
1. Thomas King Invalid
2. Thomas King (Blank)
3. Rick Hu Thomas King
4. Thomas King John Reid

Is it possible to update using Dlookup function or how i can do this, if a sample code is shown then it will be useful, tanks in advance.

 

[PHV]

Something like this ?[tt]
UPDATE Leads
SET [Lead Name] = DLookUp("[AM Manager]", "Leads", "Trim([AM Manager] & '') Not In ('Invalid','')")
WHERE Trim([Lead Name] & "") In ("Invalid","") [/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bnageshrao]

Thanks for the code, based on my actual table name and field I coded it as follows

DLookup ( [Lead_with_suspect]![ III/IV AM Name] , [Lead_with_suspect], Trim ( [Lead_with_suspect]![ III/IV AM Name] & '') and Not In ('Invalid','' ) ") where Trim( [Lead_with_suspect]![Level III/IV AM Name] & "") In ("Invalid", "").

The code looks like is going to do what I want to do, but when I coded it and ran the query I got an error which says
The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier. DO u see any missing things? Thanks

 

[PHV]

Can you please post the actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bnageshrao]

UPDATE Lead_with_suspect SET Lead_with_suspect.[Level III/IV AM Name] = DLookup ( [Lead_with_suspect]![ III/IV AM Name] , [Lead_with_suspect], Trim ( [Lead_with_suspect]![ III/IV AM Name] & '') and Not In ('Invalid','' ) ") where Trim( [Lead_with_suspect]![Level III/IV AM Name] & "") In ("Invalid", "").

This is what the sql view shows.

 

[PHV]

UPDATE Lead_with_suspect
SET [Level III/IV AM Name] = DLookup ("[ III/IV AM Name]" , "Lead_with_suspect", "Trim([ III/IV AM Name] & '') Not In ('Invalid','')")
WHERE Trim([Level III/IV AM Name] & "") In ("Invalid","")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bnageshrao]

Thanks a lot and it works. Now I have something similar to that and I dont know whether Access can handle something like this or not. Here is my problem any help is highly appreciated.

LEADOWNER AM Name AM Category AM OU
(Blank) Ed Lucas Client Com - Benefacts
(Blank) Daniel Roalkvam Client EBCG - Central
(Blank) Julie Pike Prospect Employee Benefits Outsourcing
(Blank) Cora S Silva Invalid EBCG - West
(Blank) Amy Stefancic Prospect Strategic Accounts
(Blank) Ian M Singer Client e-solutions
(Blank) Terrence Adamson Client General Compensation
(Blank) Chick Steinberg Client Executive Benefits
(Blank) Steve King Client EBCG - Southeast
(Blank) Dick A Lepanen Client EBCG - Central
(Blank) Nick Shultz Client EBCG - Central
(Blank) Dick A Lepanen Client EBCG - Central
(Blank) Brian Leighton Prospect EBCG - Southeast


Criteria

I have to update the blank values in LEADOWNER based on following conditions, I don’t know is it possible to do like this in Access.

1) Pull the first AM Name from list if the category is client and doenot have a value of invalid.
2) If multiple AM Name take the first one that has the category equal to client then take the the first one that is included in the AM OU EBCG that doesnot have a value of invalid.
3) If AM Name has no category equal to client take the first AM Name on the list that has AM OU EBCG that does not have a value of invalid.
4) IF AM Name does not have a category equal to client and no AM OU equal to EBCG then take the first AM Name on the list that doesnot have a value of invalid.