Look up data from another table

[posfog]

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

 

[welshgasman]

Simple answer would be Dlookup

https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

 

[posfog]

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

 

[welshgasman]

I can't explain it any better than the link, sorry.

 

[Joe4]

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.

 

[posfog]

Hi all,
Been looking at this all weekend and i just cant get my head around it at all :'(

 

[Joe4]

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.

 

[posfog]

Sorry

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

Price is the column in the Cost table

 

[Joe4]

Where are you doing this (Query, Form, Report)?

Note that the field names should be in square brackets:

[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.

 

[posfog]

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?