Look up data from another table

posfog

Board Regular
Joined
Jun 2, 2009
Messages
171
Hi all,
I am fairly new to Access and need some help or assistance in creating what would be a Vlookup if done in excel.

What i have so far is two tables within the same access database

Table one name Data
Table two name Costs

When someone selects a certain Material Part Number i want the lookup to look at this column in the "Data" table called and find that same number in the "Cost" table and to display the price which is also shown next to the Material Part Number within the Cost table in another cell within the same Data table

I think once i may have it explained i should be ok to work on this for other look ups that i need to do within the same database.

Hope somone can help and point me in the right direction.

Regards
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I am looking at DLookup at the moment but just cant understand how it works, as i say i am ok with excel and Vlookup etc but this Dlookup is puzzling me :(
 
Upvote 0
I can't explain it any better than the link, sorry.
 
Upvote 0
Did you look at the example in that link? They pretty much walk you through how to do it.
Give it a try, and if you cannot get it to work, post the formula you put together here, and we can probably help you sort through the issues.

Note that if you are using it in a Query, Form, or Report, you don't need to worry about the variable declaration and all that other stuff. Just enter the formula.
 
Upvote 0
Hi all,
Been looking at this all weekend and i just cant get my head around it at all :'(
 
Upvote 0
if you cannot get it to work, post the formula you put together here, and we can probably help you sort through the issues.
Can you post your formula attempt that did not work, like I asked previously? It might just be a minor tweak.

Otherwise, please post some data samples, along with the exact field names.
 
Upvote 0
Sorry

=DLookUp("Price","Cost","Material Part Number=" & [Material Part Number])

Price is the column in the Cost table
 
Upvote 0
Where are you doing this (Query, Form, Report)?

Note that the field names should be in square brackets:
Code:
[COLOR=#333333]=DLookUp("[/COLOR][COLOR=#ff0000][[/COLOR][COLOR=#333333]Price[/COLOR][COLOR=#ff0000]][/COLOR][COLOR=#333333]","Cost","[/COLOR][COLOR=#ff0000][[/COLOR][COLOR=#333333]Material Part Number[/COLOR][COLOR=#ff0000]][/COLOR][COLOR=#333333]=" & [/COLOR][COLOR=#0000ff][Material Part Number][/COLOR][COLOR=#333333])[/COLOR]
However, I have concerns about that part in blue. Where is that value coming from?
Is that the exact name of a Text field on Form or Report? If so, you may need to preface it with the "Me." keyword.
 
Upvote 0
Hi Joe,
I am doing this on a Form
Within the form i have a text box called Material Part Price and Material Part Number

Put the Dlookup in Material Part Price and want it to look at the details found in Material Part Number and bring back the price from the Cost table


Within the Cost table i have Material Part Number, Price, Material Description1 and Material Description2

Hope i am making sense?
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top