vlookup equivalent for access

j2jake
j2jake used Ask the Experts™
on
Hi guys!

Neewbie here! I have a table titled "category_sales" which have 3 columns. 2 of them blank ones titled "reconciled_category" and "dept_name". The other column which is filled is titled "category". I have another table named "dept" which have both "reconciled_category" and "dept_name" but filled and another column which is "product_category"

I need to lookup column "category" from table "category_sales" and "product_category" from table "dept". If they match, it should fill in from table "dept" to table "category_sales". It is kind of like the vlookup function in excel.

I was looking at DLOOKUP but I need to do this with the entire columns.

Another thing here is I need to assign the value "NONE" if it doesnt find any matches.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
http://www.techonthenet.com/access/functions/domain/dlookup.php
aka something like this:
update category_sales 
   set = NZ(DLOOKUP("category", "product_category", "product_category.dept = '" & category_sales.category & "'), "NONE")

Open in new window

Author

Commented:
thanks angellll! this looks great.

However, it's giving me an error on the "NONE" part. Can you please help me out with this one?
Dim sql As String
 
sql = "Update category_sales set = '" & NZ(DLOOKUP("category", "product_category", "product_category.dept = '" & category_sales.category & "'), "NONE") & "'"
 
DoCmd.RunSQL sql

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
indeed:
sql = "Update category_sales set = '" & NZ(DLOOKUP("category", "product_category", "product_category.dept = '" & category_sales.category & "'), 'NONE') & "'"

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Angel!

For some reason, I got an error with the single apostrophe at the end. Is there something that I should add?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I was missing a " after the ' ...
sql = "Update category_sales set = '" & NZ(DLOOKUP("category", "product_category", "product_category.dept = '" & category_sales.category & "'"), 'NONE') & "'"

Open in new window

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial