Microsoft Access DLOOKUP Without Programming

[@samirhabib4877]

This video has been uploade 10 years ago but it is still very useful. Thanks Richard! Greetings from Azerbaijan Republic.

[@599CD]

Oh yeah Dlookup hasn't changed in Forever

[@johnson6048]

Prior to seeing this I had watched several other tutorials on how to use the DLookup function. None of them were as simple to follow (or comprehensive) as this. The act of showing all parts involved (source table, field names, etc.) and your explanation of their use in the syntax for DLookup proved to be a true learning experience. Light Bulb! Tried it in my form and it worked perfectly.

[@599CD]

Sweet

[@katetranscribes]

This is one of the most useful (and best explained) tutorials out there, and I've seen quite a few! Thank you so much!

[@599CD]

Welcome!

[@travissimonsphoto]

You are the best teacher for Access!

[@599CD]

Thanks

[@Jojosmith342]

this is simple yet powerful. thumbs up with much thanks for the best teacher Richard

[@J35u5M0d3]

You sir are a life saver! I have been watching all of your videos trying to teach myself access. I have created a basic backend in school with mysql and MSsql but have never made a front end. THANK YOU!

[@599CD]

Referring to values on subforms gets tricky. You can't use Forms!FormName!FieldName. You have to use Forms!ParentFormName!SubFormName.Form!FieldName.

[@rajeshkumarseetharaman]

Fantastic. Thanks saved my life while in 2003 version needed to put the following way =DLookUp("[WorkPhone]","EmployeeT","EmployeeID='" & [Combobox]&"'")

[@599CD]

Welcome

[@dftdept001]

Very direct, concise, and clear to me. Kudos.

[@599CD]

Thank you.

[@fabianvillegas6313]

Thanks for all my friend, perfect for to explain VLOOKUP functions, I finished my work with your video, Im very happy

[@599CD]

Glad it helped!

[@Theburqan]

Thanks for this video, but i still have the error #Name? , I checked the Names of the source table and everything.

[@599CD]

Double-check

[@mauioreo7176]

Hi Richard, How did you come up with those tables you showed? Is there a toturial video on youtube how to make that? I am actually going to make an employee records. Please help. Cheers!

[@RochesterAreaBuilder]

Thank you! I kept running into instructions to use this formula but they weren't indicating where it went.

[@599CD]

Glad it helped!

[@michellguzelgul4267]

Impressive video. Well put together, exactly what I needed, no nonsense. Subscribed.

[@599CD]

Thank you!

[@stephaniewhitley1613]

Hi Richard -- Thanks for the video. This isn't quite going to help unless I can store the value. If the text box is bound would the value be stored? Again thanks!

[@599CD]

You'll need 599cd.com/AfterUpdate

[@histographicevents771]

This is very clear and straight forward thank you and keep it up!

[@599CD]

My pleasure.

[@mrsbillypenaflor]

Hi Richard. Thanks for this video it's been very helpful. Can you confirm that the returned value of the DLOOKUP function will be saved in the table or as part of the record where the form is actually based on? Or does it just display the returned value? Thanks.

[@599CD]

If you still need it: 599cd.com/Ask

[@599CD]

Since DLOOKUP is a more advanced topic, I assume you know how to build tables. If not, see my Beginner Level 1 class that's on my Channel.

[@ravinderbirdi5783]

Thank u so much for all the videos. They are very helpful in mastering material!!

[@hashimam2u]

Thank you for the video and i was trying this Dlookup and its not working some error is =RefName

[@nicoled9765]

Can you suggest on how I would create a query and report in Access of all employee work schedules for the week? I was hoping to make the report look like a table with the days of the week across the top/columns and hour increments along the left side/rows and all the employee names scheduled for those times and days are in the table's center. Thank you!

[@599CD]

If you still need help with this, feel free to submit your questions at 599cd.com/TH. Members get priority.

[@jacekkowalczyk6649]

Hi Richard. Thank you for this video. I will ask the same question as one of coleage below 'Does returned value of the DLOOKUP function will be saved in the table or as part of the record where the form is actually based on? Or does it just display the returned value? Can you kindly help ?

[@599CD]

It just displays the value unless you use an event like AfterUpdate to store it. 599cd.com/AfterUpdate

[@tuhinchakraborty5750]

Hi, I have a query where the balance gets updated for a particular HSN Code. I have a form with subform from where the sales entry form gets updated. I have created an unbound field in the subform to get the balance for the selected HSN code in subform from the query. I am using DlookUp function to perform this task. But the values are not getting populated in that field. Could you please help me.

[@599CD]

Feel free to submit your question at 599cd.com/TH

[@randypaul7563]

HiIn your example, the EmployeeID ia a number - could it be alpha numeric like AAA100?I am having trouble using your vlookup example with an alpha numeric iD.Regards

[@599CD]

You can use any value you like. I like AutoNumbers. 599cd.com/AutoNumber

[@VictorJimenez-bh4mn]

You are the best simple and clear

[@599CD]

Thanks

[@Chatrunner1967]

Hi, could you please explain how you created the layout from this page, I like it a lot!

[@599CD]

Watch 599cd.com/Blank

[@tutsecret499]

The result of this lookup is just visible on the fly, or this phone is stored in this box in the form? I have data in calculated field in query. I want this calculated result to be stored in the table, instead of being just visible in the query. The only way I can see this result restored in the table is I highlight the field column, copy and paste the result on the field that belongs to the table. There might be a way to avoid this routine. Maybe a macro or a vba to do the copy the field with the data from the query and paste on the field that belongs to the table.

[@599CD]

DLookup results are not stored. If you want to store them, you'll need an event like 599cd.com/AfterUpdate

[@armandogarcia6564]

Does DLookup work the same way for combo boxes...will it lookup multiple values from a table and show them in the drop down?

[@599CD]

Nope. DLOOKUP will only return one value at a time. You'd need a loop to look up multiple values, and DLOOKUP probably isn't the best tool for that job.

[@armandogarcia6564]

@@599CD Thank you very much Richard. I actually ended up using 1 line of VBA code/SQL statement to populate the drop down options for my combo box.....Something Like this: Me.FormField.RowSource = "SELECT* FROM MyTable WHERE TableTypeID = " & Me.TypeIDFormField I really appreciate your youtube videos and your style of teaching by the way, thank you very much. Where do you teach?

[@jay55patel]

One million time thank you. You are the best. i am learning lot from you

[@599CD]

Welcome

[@linhanwen2219]

I realize this video is using access2013, for those using 2010 do ammend your Dlookup to: =DLookUp("WorkPhone","EmployeeT","EmployeeID='" & [Combobox]&"'") There should be a apostrophe after the "EmployeeID=" and do take note that your data base name field should't have spacing. Just sharing :D been troubleshooting my mistake for a few hours.

[@arturssolomencevs3700]

Thanks ;D

[@poohpoohgirlsj]

+Lin Hanwen THANL YOU!!!!

[@oltroman1]

Lin Hanwen Thanks you :)

[@dindomendoza2634]

Thank you for sharing....

[@cynthiageorge6278]

And 7 years later, you are still the hero! Thanks!

[@tutsecret499]

I have a table CONTACTS where I input patients, street, city, county when I choose city from the combo box, I want to feed the neighbor column COUNTY as Orange. The table outside my CONTACTS tables has 3 columns: IDCity, City, County (=Orange). So do I have to create another field besides the Cities column to do the lookup feature.

[@599CD]

Not if you only want to DISPLAY it.

[@jayrey311]

Hello. Please accept my apologies if my inquiry is in the wrong place. I would like to know how i can lookup existing data (computer sn#) in a table and somehow create a combobox that shows if the computer serialnumber was already used or assigned to a component (dockingstation sn#). If the computer sn# isnt used, it will auto populate in the field (compsnnumber) and then i will manually enter the sn of the docking station in another field (DSSerialNumber). I tried querying the computer sn# with dsserialnumber but somehow the query isnt populating the dsserialnumber info into the combolookupbox. its only showing in the dsserialnumber field. any help is greatly appreciated.

[@599CD]

I'm sure it's possible. Probably need to program an event. 599cd.com/AfterUpdate

[@genius53]

Excellent tutorial! very well explained. Thank you!

[@599CD]

Welcome

[@SirEmbrooos]

Pse help me compare too fields each with different data eg labtests and other payment companies IDs to lookup the costs which are different also accding to the Ids

[@599CD]

I'm sorry, but you'll have to be a little more precise.

[@wink1275]

This is a great tutorial, BUT I can't get it to work. I am using Access 2007 - 2016 file format, would this be why?

[@599CD]

Nope. DLOOKUP works wayyyyyy back in Access 2000 and before. Start over from the top.

[@slhangen]

How did you get the rep name to display when the field called for salesRepID?

[@599CD]

It's a combo box. The first (hidden) column contains the SalesRepID. The second (first visible) column shows the name. See the COMBO BOX TUTORIAL that I left a link for in the video description.

[@louie115]

awesome video...awesome teacher! thanks for the lesson!

[@599CD]

My pleasure

[@MrMeLaX]

Thanks for your video. But how do I remove the error message when "Sales Rep" is not chosen?

[@599CD]

Gotta have one. If you don't want one, then don't make it required.

[@Iicc7767]

Please help! I want t create a form in access 07 , which is everytime the form is open, the order box fill in with a new auto increase number . thank you!

[@599CD]

Yeah, that's called an Autonumber. 599cd.com/Autonumber

[@FireObama2012]

I'm looking to calculate commission pay at one rate during the weekdays and at another rate during weekends and this based on the total labor sold during a given period. I've managed to figure out how to take my imputed date and turn it into a "spelled out" day of the week in a query but the next step is to use a formula that takes into consideration the day of the week to determine when to apply the weekday rate or the weekend rate in order to calculate total commission earned for the pay period.

[@599CD]

599cd.com/ask

[@glens1975]

can someone advise on an issue I am having I am using =DLookUp("CostPrice","ProductT","ProductName=" & [ProductName]) whereas in a form I am wanting to enter into the text box named CostPrice the value from the field CostPrice in the table ProductT from the field ProductName eg I have a dropdown list for productname so i select an option, i want it to briong in the cost price with both productname and costprice being within the productT table i keep getting an error and am not sure why. Is it like excel where your lookup vale needs to be to the left of the field to grab ???

[@599CD]

Quotes around text criteria

[@bbmak0]

Can you do a multi dlookup criteria?

[@linhanwen2219]

Yes you can, for example: "Name='" & [Combo39] & "'AND Month='" & [Combo49] & "'"

[@NarminJBNarmeenbizzle]

sir it is show to me it is wrong the message it show is :the expression you entered contains invalid syntax how can I fix it

[@599CD]

It's impossible for me to tell what you did wrong if I can't see... what you did wrong.

[@NarminJBNarmeenbizzle]

@@599CD how can i get to contact with u to send you photo

[@NarminJBNarmeenbizzle]

@@599CD this is code that I wrote =DLookUp("workPhone"," EmployeeT","Employeeid=" & [salesRepCombo])

[@NarminJBNarmeenbizzle]

@@599CD I sent to u an E-mail message sir

[@husseinfarah4452]

Hello I did the same steps =DLookUp("CellPhoneNo","TblEmployee","EmployeeID=" & [EmployeeID]) ... but it not showing the phone number till i have to click inside the box then numbers appears

[@599CD]

Click inside what box?

[@TheFryPo]

Thank you this is a huge help !

[@599CD]

My pleasure.

[@EfrenCorona]

I've recreated everything even as shown (fields, tables, combo box names), and it's still coming back with #Name? error. There's something you're missing that you may not have disclaimed.

[@599CD]

Nope. It's pretty straightforward. I've been teaching this example in my classes for 20 years now, and as you can see, that video was posted 7 years ago and nobody else has complained. The most common error is that you've misspelled something.

[@priyas9751]

I'm having some trouble with this. I'm trying to use DLookup to get the Title of a drawing based on the Drawing Number (of data type text), which is selected from a combo box. In the Title field on my form, this is my formula: DLookUp("Drawing Title", "Drawings","Drawing Number="&cboDrawingNumber) This formula gives me an error. Can anyone help me with this?

[@599CD]

Spaces in your field names will get you every time. [Drawing Title]

[@javadrajabi9639]

hi sir I want to used dlookup in subform but it's not correct result -i want used dlookup in main form but refer to subform datasheet please help me java

[@michaelfluss3750]

Wow i like your Tipps. Greedings from germany.

[@599CD]

Thanks. Cheers from Florida.

[@theanimalqueen8610]

Can someone help me? I've watched this video, I've searched the web for other advice and even tried to adjust accordingly with some of the comments below with no success. I am using Access 2007. Here is how i have it currently entered. =DLookUp("Tool Diameter inches","Tooling","Tool="" &[Tool Combo]&") Any help is greatly appreciated why I still have hair. lol

[@599CD]

Scott Melton If your Tool combo box holds a NUMBER value, you don't need the extra quotes. Plus, I recommend square brackets around any field name that has spaces in it (which you should try to avoid): =DLookUp("[Tool Diameter inches]","Tooling","Tool=" & [Tool Combo])

[@theanimalqueen8610]

599CD Computer Training I greatly appreciate your help. First the Tool Combo is not a number value, but the values I'm trying to pull in are numbers. Now, I copy and pasted the formula you typed in above and as soon as I tried to enter I got a message stating the expression entered contains invalid syntax. Please help!

[@theanimalqueen8610]

Scott Melton Maybe it helps but I created a new form and pasted your formula back in the control source of a text box. No syntax error this time but I still get #Name? in the field.

[@MalikBabarHasan60855]

Excellent, amazing. Thanks

[@599CD]

Thank you too!

[@dindomendoza2634]

Very helpful thank you...

[@599CD]

You're welcome!

[@Zzgr19]

Thank you for all the tips. Won't VLOOKUP work too? Kinda easier to use those imo 😅

[@599CD]

Yep. If this were Excel. But it's not. It's Access. Access doesn't have a VLOOKUP. Although I do show you how to create something similar: 599cd.com/VLookupInAccess

[@firmanhabibi7970]

I WANT TO PAY TO THE COURSE BUT I DONT HAVE A CREDIT CARD, IS THAT A WAY TO PAY WITHOUT CREDIT CARD TO UNLOCK SEVERAL VIDEO'S??

[@599CD]

I accept other payment methods on my web site: 599cd.com/THMember

[@تاجالمحتاج]

Thank u it's on time

[@599CD]

Welcome

[@Rojin.Marzooki]

Great very helpful

[@599CD]

Glad it was helpful!

[@imrancapital8419]

Richard, if I ever get an opportunity to meet you.. I will salute you in person... You are simply the best..

[@599CD]

Sounds good. :)

[@sekkaali]

Useful Lesson

[@599CD]

Thanks.

[@sanilkumarv575]

SIr I tried with LOOKUP fn. But it not working

[@599CD]

If at first you don't succeed... :)

[@elizabethofori1165]

Thanks a million

[@599CD]

You're welcome

[@asaburrahman8947]

Nice Job, Thanks

[@599CD]

You're welcome

[@stephonnechien6144]

my textbox endedup showing #name? as well... but i thought i followed everything

[@599CD]

Try again. Double-check your spelling.

[@innovation7273]

HOW CAN LOOK UP ATTACHMENT FILE

[@599CD]

What do you mean by "look up?"

[@armandogarcia6564]

You are awesome

[@599CD]

Thanks!

[@chanchaldas6229]

Thank you...

[@599CD]

Welcome

[@danielleung264]

valuable

[@599CD]

Thanks

[@qahtaniinvoices428]

SORRY SIR THIS FUNCTION NOT WORKING PLEASE

[@599CD]

Double-check all of your spelling. What result are you getting? Error message? I need more info. This video has been online for 7 years and has over 140,000 views, so it's got to be something you did incorrectly. I've been teaching this same exact function for 20+ years.