Getting a value from a table: DLookup()

jsy165

标题：Getting a value from a table: DLookup()

来源：http://allenbrowne.com/casu-07.html

原文：

Sooner or later, you will need to retrieve a value stored in a table. If you regularly make write invoices to companies, you will have a Company table that contains all the company's details including a CompanyID field, and a Contract table that stores just the CompanyID to look up those details. Sometimes you can base your form or report on a query that contains all the additional tables. Other times, DLookup() will be a life-saver.

DLookup() expects you to give it three things inside the brackets. Think of them as:   Look up the _____ field, from the _____ table, where the record is _____

Each of these must go in quotes, separated by commas.

This is probably easiest to follow with some examples:

you have a CompanyID such as 874, and want to print the company name on a report;

you have Category such as "C", and need to show what this category means.

you have StudentID such as "JoneFr", and need the student抯 full name on a form. Example 1:

Look up the CompanyName field from table Company, where CompanyID = 874. This translates t   =DLookup("CompanyName", "Company", "CompanyID = 874")

You don't want Company 874 printed for every record! Use an ampersand (&) to concatenate the current value in the CompanyID field of your report to the "Company = " criteria:   =DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID])Example 2:

The example above is correct if CompanyID is a number. But if the field is text, Access expects quote marks around it. In our second example, we look up the CategoryName field in table Cat, where Category = 'C'. This means the DLookup becomes:   =DLookup("CategoryName", "Cat", "Category = 'C'")

Single quotes within the double quotes is the easiest way to do quotes within quotes. But again, we don't want Categoy 'C' for all records: we need the current value from our Category field patched into the quote. To do this, we close the quotation after the first single quotemark, add the contents of Category, and then add the trailing single quotemark. This becomes:   =DLookup("CategoryName", "Cat", "Category = '" & [Category] & "'")Example 3:

In our third example, we need the full name from a Student table. But the student table has the name split into FirstName and Surname fields, so we need to refer to them both and add a space between. To show this information on your form, add a textbox with ControlSource:   =DLookup("[FirstName] & ' ' & [Surname]", "Student", "StudentID = '" & [StudentID] & "'")

   译文：从表中获得一个值：DLookup()迟早，你需要从一个表中找回一个值。如果你有一个公司的正规货物表，你需要一个包含公司的详细资料里面包括公司编码字段和一个表。它通过公司编码去查找一些详细资料。有时候你能够基于你的窗体或报表一个查询能够包括所以附加的表，其他的时期，DLOOKUP将是一个有利的帮助者。DLookup()期望你添三个东西在里面的括弧里，想象像下面一样：Look up the _____ field, from the _____ table, where the record is _____像这样的都必须单独用逗号去引用它们。这种可能性可以通过下面的例子来学习：1.       你有一个像874的工作ID，然后想要在报表上打印工作名称。2.       你有一个像C一样的种类，然后需要出示种类的含义。3.       你有一个像JoneFr的学生编码，然后需要显示学生全名在窗体中。例一：从公司表中查寻公司名称字段，where CompanyID = 874这句翻译为：=DLookup("CompanyName", "Company", "CompanyID = 874")你不想要每一条记录都打印为公司874。使用记号名称”&”在公司报表的"Company = "的公司编码字段中连接当前值=DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID])例二：如果公司编码是一个数字，上面的例子

fautc

?