Lookup?

[Von Pookie]

I got bored, so imported one of my larger Excel files to Access to play around with it (Access XP, by the way).

I have a certain field that has an id entered into it which corresponds with the manager that should receive the file based upon that number. I have a separate list with those names/numbers.

How can I make it so that when I enter the number in the one field, that it will automatically input the corresponding manager name?

I know I could accomplish this in Excel with a simple VLOOKUP. And I'm assuming there has to be some sort of equivalent in Access, but what? I found something called "Autolookup" in the help file, but can't seem to wrap my head around how this works.

Any ideas?

 

[bat17]

You do this is Access by Joining fields. If you create a query with both tables in it you can join your Managers ID field in the data table to the Managers ID in the lookup table. if you then add the fields you need from each table to the query grid you will find that they fill in automatically when viewed.

Another way of doing it is to use the Lookup Wizard in the table to create the link to the managers ID. In your data table design view go to the Managers ID filed and for data type select LookUp Wizard. This will let you select the table that you want to link to and the fields you want to show, in this case ID and Name, it will then create the link for you. Where you have an ID and a description it will hide the ID automatically so that when you use the combobox it creates you will just see the manager’s name. One thing to remember is that whilst it shows the manager’s name it is actually storing the ID number, this can cause issues with data types if you forget and then try to treat it as a text instead of a number in code.

To answer your actual question DlookUp is the equivalent of VlookUp, There is no equivalent to Hlookup.


HTH

Peter

 

[Von Pookie]

Hrm. That didn't help me much. Not your fault, though. You did explain it, which is great. I just can't understand it.

And today I have come to a decision: The Access help file is *not* written for any sort of entry-level usage. Sheesh.

 

[u0107]

Help text

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")

Replace CompanyName with the name of the manager
Replace Shippers with the name of the table
Replace ShipperID with the ID

Note your table will need to be indexed on ID

Also ID and name of the managers should match the field names

Hope this helps.

Cheers!

Uttam