• Microsoft Access Dlookup Multiple Criteria
• Microsoft Access Dlookup Function

Active7 years, 1 month ago

DealID is set using a Combobox and it needs to Display up to 3 MID's in the field below it

DLookup multiple criteria Modules & VBA. Access World Forums > Microsoft Access Discussion > Modules & VBA: DLookup multiple criteria.

That's the code I'm using the obvious limitation is Dlookup only returns the first result it finds.That creates 2 problems but Ill focus on the first, It won't display more than one MID

so how I can get the 1-3 MIDs to be displayed?

1. If you ask ELookup() to concatenate several (not memo) fields, and more than 255 characters are returned, you strike this Access bug: Concatenated fields yield garbage in recordset. DLookup() can call the expression service to resolve an argument such as: DLookup('Surname', 'Clients', 'ClientID =.
2. Friday, 29 July 2011. The DLookUp function allows the Access Developer to look up the value of a field from a table other than the form's actual Record Source. It is often used as a function in a Calculated Text Box Control (see previous post for more information about Calculated Controls ). So applied to our scenario.
3. Is it possible to return multiple values with one dlookup statement? AccessForums.net is a forum dedicated to Microsoft Access, if you want to ask any Access related questions or help other members out, please join our community, the registration is free and takes only one minute.
4. Home Newsgroups > Microsoft Access > Microsoft Access Macros > Dlookup using Multiple fields from a table Discussion in 'Microsoft Access Macros' started by chinaboy992, Feb 29, 2004.

The second issue I have is more in-depth but if anyone wants to help, a personal chat would be appreciated. Basically The form above is a child form and I need it to save a separate entry in the forms table for each Mount ID.

If anyone would like to help but doesn't understand(as is often the case with my submissions), I think screen sharing on Skype is the best option.

Fionnuala

85.7k77 gold badges9696 silver badges133133 bronze badges

Matthew Optional MeehanMatthew Optional Meehan

36944 gold badges88 silver badges2020 bronze badges

1 Answer

As already said in a comment, you can't use DLookup to return more than one value.

You need to select the first three MIDs into a Recordset, loop through them and append them to Me!MID:

Microsoft Access Dlookup Multiple Criteria

Note that this example uses DAO, which is the default (and recommended by MS) data access technology in newer Access versions. Older versions used ADO as default.
DAO works there as well, you just need a reference to Microsoft DAO x.x Object Library.

Christian SpechtChristian Specht

30k1313 gold badges102102 silver badges162162 bronze badges

Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.

Not the answer you're looking for? Browse other questions tagged ms-accessvbaaccess-vba or ask your own question.

In Access desktop databases you can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control on a form or report.

Note: This article doesn't apply to Access web apps.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

Syntax

DLookup(expr, domain [, criteria] )

The DLookup function syntax has these arguments:

Argument	Description
expr	Required. An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain	Required. A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria	Optional. A string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

Remarks

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value: