How to Use DLookup in Microsoft Access

[@balbumohshaba3588]

Thank you sir for the tutorial. It's simple and easy to understand,

[@seanmackenziedataengineering]

Glad it helped!

[@sarak4617]

Great video, thank you! Can you use DLookup with a continuous form? I have a continuous form and I would like to display the value from another table with each corresponding line of the continuous form. I have an identical linker in each table and I am using the continuous form as a sub form within another form for data entry (all fillable fields are from one table only, I want to display a value from another table along side the fillable data). I keep getting a #NAME? Error. Any suggestions on what I am doing wrong? I have checked over spelling many times.

[@seanmackenziedataengineering]

You bet! Yes you can do this, but you need to specify the field name on your form for the right parameters. For example, I did this one where the where clause needs quotes around the alphanumeric ID in the DLookup (you can also use multiple double-quotes instead of Chr 34): =DLookUp("[Country name]","CLIMATE_CHANGE_DATA","[Country code] = " & Chr(34) & [Country code] & Chr(34))

[@grayquiksilver]

Another great tutorial - thanks Sean!!

[@seanmackenziedataengineering]

Thanks!

[@stanTrX]

Hello Sean, thank you for the video. Which one would you recommend (considering performance etc) using dlookup in form level or in query level? Btw, does it also work properly in the form level, for continuoss forms also? (I remember some repetation problem i had before but maybe i was doing something wrong)

[@seanmackenziedataengineering]

It works on continuous forms and in queries. In continuous forms, you need to do the filter to the current record ID value or you will see duplicates. Either way is fine!

[@stanTrX]

Sean, can you please give link to your video you mentioned here 14:31

[@seanmackenziedataengineering]

This is the video on lag and lead in MS Access: kzbin.info/www/bejne/b5q2pnxpmbdqa9E You can see how slow it will be with DLookup, if you have many rows. With just a few rows, it is fine though.

[@stanTrX]

@@seanmackenziedataengineering thank you 🙏

[@letitiablake7243]

Good afternoon. I have created inventory management for reselling mostly one-off items. I want to enter the product SKU in a textbox to look up the SKU of the product. Then have it return the record and all associated fields on the form. Can you suggest any video to me accomplish this? Thank you for your help.

[@seanmackenziedataengineering]

Hi Letitia, you can do something similar to this, except use a "columnar" form instead of a continuous form: kzbin.info/www/bejne/q6KWlJqXfatpnMU The combo box will auto fill the SKU as you enter it, so eventually it auto-fills and you can select your SKU and the form updates.

[@nemo9396]

What if my price is in a separate lookup table? Somehow I cannot make the Dlookup return the value in a different field, i.e. the foreign key.

[@seanmackenziedataengineering]

That is ok! Perhaps fk = DLookup("key", "another table", "productname = 'abc'") Then price = DLookup("price", "this table", "key = '" & fk & "') Does that work?

[@caloebs]

What does "1=1'" in the criteria argument mean/do? Dlookup('field','table',"1=1") - thanks for the video

[@seanmackenziedataengineering]

1=1 is a starter expression that developers sometimes use when they are going to build a big dynamic Where clause on their query. If the user selected no criteria, then they can still use their SQL statement which returns all rows as their clause will be Where 1=1, which means all records. If a user chose something, then Where 1=1 And Field1 = 'ABC' will return additional filter for Field1, Where 1=1 And Field1 = 'ABC' And Field2 = 'DEF' etc. It is a lazy solution for the situation where you don't control whether the Where clause can be included or not (it must be included, but what if the user chooses nothing?)

[@caloebs]

@@seanmackenziedataengineering it looks like the prior programmer just wants the first record in the Table. I don't understand his logic but I guess that is what would happen with 1=1, correct? And many thanks

[@seanmackenziedataengineering]

@@caloebs actually it just means True, allowing all records to be included. If you put 0 = 1 then it will kill the return set unless you use Or in the where clause. Funny hey?

[@mosjany]

Kindly i have a form frmivoice that has asubform frminvoicelines,how do I populate items from inventory table and prevent negative stockings

[@seanmackenziedataengineering]

Create a dropdown or text field and after the user chooses an item from inventory, check if there is inventory to fill the order using After Update (you can also use Before Update). If there isn't enough inventory, show a message and then clear the selection on that line. You can use this command: kzbin.info/www/bejne/qaW0maV8jpmebqc

[@tamalchakraborty1277]

Property sheets of all forms of a MS Access database are locked. How to unlock it?

[@seanmackenziedataengineering]

What is the file extension? Is it .accdb or .accde?

[@tutsecret499]

DLookup and After Update. Instead of selecting, or typing from the cbo field. If I import or paste countries would the field cboCountry work, and populate the RaceGroup? Example: I have cboCountry, and populates the RaceGroup textbox. example: if it's typed or paste or select from cboCountry the Phillipines country, the RaceGroup would populate Filipino and so forth. So instead I click, select from the dropdown cbo, Sometimes I receive hundreds of rows to import where with countries and individuals, and the RaceGroup field needs to be populated with the RaceGroup: White or Hispanic, Filipino and so forth. I have to use excel to use the vlookup or xlookup to populate the RaceGroup in Excel then I import everything ready to Access. But I want to do this in Access. It's ridiculous we have to use Excel to help Access. This happens to other people too. The hand aches suffers injuries if clicking too much on the combo box and selecting when dealing with too much data daily.

[@seanmackenziedataengineering]

If you imported the table with the country and group, you can use DLookup and After Update. Go for it! That will save you some typing. There are two kinds of After Update and you will use this one: kzbin.info/www/bejne/qaW0maV8jpmebqc Also, if you look closely, at 2:41 I briefly show DLookup with two conditions in the Where clause, in case you need Field1 = "abc" AND Field2 = "def". No need to hurt fingers with too much typing!

[@tutsecret499]

@@seanmackenziedataengineering When I import or append data. There is no RaceGroup, only the countries, thus I need to populate the RaceGroup that is blank in my database that is blank. The imported/appended data is from external source and they don't provide the RaceGroup only countries. Thank you.

[@omarg5759]

Thanks Sean for the tutorial. Though I just have this challenging task, where there this two-tables database, the first one is linked from CSV and the other is just a table. The first table has column of two digit product codes and labels in the same cell, and the second has product codes on a column and different column for labels, which is the correct one I want to retrieve. What I tried to do is match the first two characters of the first table values with the codes of the second table through -> Dlookup(“Product labels”, “second table”, “left([Product codes and labels], 2) =“ & [Product codes]) but it didn’t work. Is there a way to do it through dlookup or I’m way too optimistic to try not to creat another query then link them?

[@seanmackenziedataengineering]

If you have codes and labels in one field, you can make a query on the first table and split those like this: kzbin.info/www/bejne/rIK7YaeXhbZ_eJI Then, you can just use a dlookup on the product code, or create a query that joins by product code. You're better off to make a second query that gives all the rows you want using a join instead of dlookup, as dlookup might be slow if it is executed on every row. Good luck!

[@omarg5759]

@@seanmackenziedataengineering Thanks a lot Sean for the reply and other structured tutorials. Happy holidays!

[@afrinsir7479]

dlookup is good but too slow is there any other technique for that ?

[@seanmackenziedataengineering]

Great question! DLookup will indeed be too slow, especially if you use it in a query where it must execute on every row. For those cases, you should use a set-based approach instead. You can see examples here: kzbin.info/www/bejne/a5TRqHuvo5mXpKs

[@satyabanukil779]

Sir a very small and simple query. Does "On Got Focus" work in a tabular form ?

[@seanmackenziedataengineering]

You can test it - put a MsgBox in there and see if you can trigger it. Give it a try 👍

[@satyabanukil779]

@@seanmackenziedataengineering Thank you Sir. On Got Focus working in tabular form also. Still not successful in implementation of your advice sent 3 days ago on using "On Current" event. Sir if you get time may kindly look at that. Regards

[@HenrySen-Opoku]

Sean, I love your videos. Can you use DLookup to look for values in an entire field not a specific value in a field. Eg. DLookup("AmountPaid","tblMain", "[Date]=Year(Date())-1)" . (Previous Year Payment) This code seeks to lookup for the values of the entire field but only works on the first record and repeat the same value on the other records. Maybe DLookup is not the appropriate code for this function, please advice me.

[@seanmackenziedataengineering]

Great question - DLookup is indeed only for one value. It looks like you are using DLookup in a query and just need one more criteria. ie. "[Date]=Year(Date())-1) And [CustomerID] = [IDField]" If in the same table, CustomerID and IDField might have the same name.

[@henrysen-opoku590]

Thank you. You are the best. Will try the suggested codes and get back to you.@@seanmackenziedataengineering

[@sakalansnow6579]

Great thanks

[@seanmackenziedataengineering]

You are welcome!

[@YvesAustin]

Sean, as always great to review your videos. I am wondering if you have covered a topic equivalent to an Excel VLOOKUP() with a TRUE argument at the end (i.e. approximate match ascending sorted criteria). I am essentially trying to build a query where the related field should be based on an inequality: for bin sizes from 1 to 10 return "small", from 11 to 20 return "medium", from 21 t0 30 return "large", etc. As you can guess, the related table is much larger with about 15 to 20 bins. I initially attempted creating the inequality inside the SQL statement itself; it kind of worked for a while but crashes in a runtime version of Access and is not very robust (I will get runtime errors). I could create a master table with all sizes from 1 to 1000 with each their corresponding category, but that sounds like overkill. Managing a bunch of nested IIF() functions does not seem very efficient neither. Any tips from your end? Thanks again!

[@seanmackenziedataengineering]

Great question. For this scenario, you can just plug a VBA function with Select Case in it. Something like: Function BinCategory(varValue) As String Dim lngValue As Long Dim strBinCategory As String lngValue = Nz(varValue, 0) Select Case lngValue Case Is > 30: strBinCategory = "X Large" Case Is > 20: strBinCategory = "Large" Case Is > 10: strBinCategory = "Medium" Case Else: strBinCategory = "Small" End Select BinCategory = strBinCategory End Function I did that in the comments editor so it might have mistakes but you get the idea. Then, in your query, just plug it in a field: MyBinCategory = BinCategory([BinCount]) And you're done! The order on the Select Case is important, so that it exits in the right place. ie. if you put Case is > 10 as the first Case then everything will be Medium and Small! Cheers

[@YvesAustin]

@@seanmackenziedataengineering Thanks for the quick reply. Yes, I will def use that criteria which is straightforward. I should have clarified that my initial inequality in the SQL statement was on the LEFT JOIN itself not in the WHERE clause. And that is why it certainly created errors (although it is an interesting exercise to attempt creating a join using an inequality!!).

[@seanmackenziedataengineering]

@@YvesAustin That's a great exercise for SQL. While I rarely use less/greater than in joins, I see it in the wild a fair bit. Another neat one is leaving the join syntax out but then putting the join in the Where clause. It makes you realize the many different ways that one task can be accomplished!

[@tomscroggin6582]

After all of the explanation, the one thing I need is the proper expression. Can't see it! Curser is on top and you didn't expand the text box enough so the expression can be read. Final got it, but it didn't work in my database.

[@seanmackenziedataengineering]

Check out the big red letters at the bottom of the screen at 6:25 - I put the expression I'm typing there so you can see it.

[@HenrySen-Opoku]

Hi, Sean. Plaese, still waiting for your reply.

[@seanmackenziedataengineering]

Replied on your other thread

[@harshmancegroupllc4517]

Hi Sean, I am trying to geta Dlookup to find a price from another table but it is not working and has a #Error come up in the query table. I am trying to get [Price] from table "WorkPerformedPrices" by matching [Work Performed]. My code is: WPrice: DLookUp("Price","WorkPerformedPrices","Work Performed=" & [Work Performed]) Is the space in work performed causing the issue?

[@harshmancegroupllc4517]

When I click on the error box it says: Syntex error (missing operator) in query expression "Work Performed=Engineer" where Engineer is what is in the work performed box.

[@seanmackenziedataengineering]

If [Work Performed] is text, you would need the last argument to be: "[Work Performed] = '" & [Work Performed] & "'" Note the single and double quotes used. Good luck!

[@harshmancegroupllc4517]

@@seanmackenziedataengineering Thank you so much!!