By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,236 Members | 1,598 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,236 IT Pros & Developers. It's quick & easy.

Get field value from related table

P: 5
Hi,

I am working with Access 2010 and I want to get a field value from an related table and put it into an text field in an report. I tried something like DLookup, but I get this error: #Type! (I am working with the dutch version).

Please give me another, working solution and instructions/code.
Jul 27 '10 #1

✓ answered by Stewart Ross

Sorry, but I can't tell from the screenshot - it's not got the resolution to let me see where you are typing this.

If you are using an unbound textbox then the property into which you type the =Dlookup expression is the control source for the textbox. As I have said, commas are the separators. Here is an extract from the MS Help entry for DLookup:

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "Products", "[ProductID] =" _
& Forms![Order Details]!ProductID)
-Stewart

Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Sorry, but as you have posted no details of the field you are trying to look up, the table or query in which it is located, and the WHERE clause you are trying we can't help you. You should at least have posted the exact call you are using for the DLookup that fails!

Dlookup, like all the domain functions, is straightforward to use but it must be called in the correct format, of which this is just an example:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[YourFieldName]", "[YourTableName]", "somefield=somecriterion")
-Stewart
Jul 27 '10 #2

P: 5
I am making an database to collect my school points. I have an table that is called subjects and a table ths is called tests. The subjects table has the full name of the subject, and in an report of the table tests, i want to get the full subject name, not just the id.

I tried it again with your code:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Name]";"[Subjects]";"[Subject-Id]="+[Subject])
But he gives again this error: #Type!
Jul 28 '10 #3

Expert Mod 2.5K+
P: 2,545
Not surprised - the arguments to DLookup must be separated by commas for a start, and you should use the ampersand operator to concatenate the value concerned onto the where-part string:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Name]", "[Subjects]", "[Subject-Id]=" & [Subject]
This should work OK if the [Subject] field is a number. If it is a text or alphanumeric value, you will need to enclose it in single quotes, like this:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Name]", "[Subjects]", "[Subject-Id] = '" & [Subject]& "'"
-Stewart
Jul 28 '10 #4

P: 5
If I use comma's,he says that there is an syntax error.

Maybe I am typing it on the wrong place?I added a screenshot.

Naamloos.jpg
Jul 28 '10 #5

Expert Mod 2.5K+
P: 2,545
Sorry, but I can't tell from the screenshot - it's not got the resolution to let me see where you are typing this.

If you are using an unbound textbox then the property into which you type the =Dlookup expression is the control source for the textbox. As I have said, commas are the separators. Here is an extract from the MS Help entry for DLookup:

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "Products", "[ProductID] =" _
& Forms![Order Details]!ProductID)
-Stewart
Jul 28 '10 #6

P: 5
@Stewart Ross Inverness
Thank you very much. It works now on the way I want.
Jul 28 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.