Dlookup in one table looking for a value in a second table

[gheyman]

My first Table has two columns: "PROGRAM NAME" and "PROGRAM ID". The table Name is "PROGRAM_LIST"

On my second table, where I am having difficulty, I have a field built with the Lookup Wizard. It puts the List of Program Names in the field named PROGRAM NAME (Same Name different table). In the Next field I want to do a Dlookup to pull in the "PROGRAM ID".

No matter what I try, I keep getting an error. I am putting the formula in the Expression section.

=Dlookup("Program ID","PROGRAM_LIST","[PROGRAM NAME]="PROGRAM NAME")

 

[Joe4]

A few things:

- Remember what I said yesterday, you do not use square brackets in these "D" functions when referring to the Table name or field you want to return. The only time you would do it is if you were returning the value from a Textbox from a Form in your Criteria.
- Don't do calculated fields in Tables. You cannot actually use the DLOOKUP function in a Table calculated field anyhow. I don't believe Table calculated fields can look outside of the Table they are located in. Do all calculations in Queries.
- Using DLOOKUP, in the Criteria argument (third argument), if the value is Text, you need to use Text Qualifiers (single or double quotes).

Take a look at this link here, especially "Example 2", which shows you two ways to do text criteria (one for hard-coded criteria, and the other for criteria in a textbox on a Form):
Microsoft Access tips: Getting a value from a table: DLookup()