How to populate field from another field bvased on criteria 1

[bnageshrao]

I have a table called Leads in access database with about 25 fields. One of the field is “Lead Owner” and the table has another field called “AM Manager”. If the value in field Lead Owner is either invalid or blank then the first AM Manager field is populated in the Lead Owner field, if the first AM Manager has a value of invalid then the next AM Manager which has valid value is pulled into Lead Owner field. How this can be achieved in access database, any help is highly appreciated in advance.

 

[PHV]

Perhaps with the IIf function ?
What is an invalid Lead Owner value ?
What is an invalid AM Manager value ?
What is a next AM Manager ?

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

 

[bnageshrao]

I will explain with an example. Let us say the lead table has two columns with values as shown below.

Lead Owner AM Manager
1. Dan Russ Invalid
2. Invalid Jason Roth
3. Rick Hu Thomas King
4. (Blank) Invalid

The value for 2nd row is invalid so it must be replaced with the first value in AM Manager. But the first value in AM Manager is invalid and hence the next value is Jason Roth.
The value in 4th row is Blank and hence it will be replaced by Jason Roth also since the first value in AM Manager was Invalid.

The columns should look like this after manipulation

Lead Owner AM Manager
1. Dan Russ Invalid
2. Jason Roth Jason Roth
3. Rick Hu Thomas King
4. Jason Roth Invalid

Can this be done using Iif function, if so please write the sample expression so that I can try. Thanks

 

[PHV]

What is the rule (sequence order) saying that Dan Russ is 1st record, Invalid 2nd, ..., (Blank) 4th ?

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

 

[bnageshrao]

Hi PH

The lead table will be built in access by importing an excel file, there is no perticular order for the records, I just showed an example. All I have to do is if the lead owner column has an invaid value or blank value, then i should find a valid value from the AM manager column and populate it. Thanks for helping me out.

 

[PHV]

Something like this (SQL code) ?
UPDATE [lead table]
SET [Lead Owner] = DLookUp("[AM Manager]","[lead table]","[AM Manager]<>'Invalid'")
WHERE Trim([Lead Owner] & "") 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