Using a display value in an expression

[sshepard]

I have a table that contains the IDs and concentration of up to three additives (Additive1ID, Additive2ID, Additive3ID and Weight%1, Weight%2, and Weight%3). The additive IDs are linked to another table of additives that displays the name, supplier, etc. of each additive. I am currently trying to create an expression in a query that concatenates the additive and concentration fields so that I might create a field that displays:

"3% Additive A, 2% Additive B, 4% Additive C"

Currently the expression is:

[Weight%1]*100 & ”% “ & [Additive1ID] & " " & [Weight%2]*100 & ”% “ & [Additive2ID] & " " & [Weight%3]*100 & ”% “ & [Additive3ID]

Unfortunately, that only displays the ID of the additive (the bound value), not the additive name (the display value) so I end up with a field that looks like this:

"3% 1, 2% 2, 4% 3"



How can I rewrite my expression so that the additive names, not their IDs are displayed? Thanks very much for you time!

[apr pillai]

[Weight%1]*100 & ”% “ & "Additive " & CHR(64+[Additive1ID]) & " " & [Weight%2]*100 & ”% “ & "Additive " & CHR(64+[Additive2ID]) & " " & [Weight%3]*100 & ”% “ & "Additive " & CHR(64+[Additive3ID]) 

[sshepard]

The code that you posted would work if the additives were really named Additive A, Additive B, etc. Unfortunately, the additives have actual names, like sulfur or phosphoric acid, that are in the AdditiveName field of the additive table. My question is, given the table of Additive IDs and names that I have, can I somehow "look up" the AdditiveName that corresponds to a particular Additive ID in this expression?
Thank you for your prompt reply and I'm sorry that it wasn't exactly what I was looking for.

[sshepard]

So I’ve determined that DLookup may be the key to accomplishing what I’ve set out to do. Some preliminary orientation before I jump right in to code: Additives is a table containing the fields AdditiveName and ID, the latter of which is the table’s primary key. All Samples is a table that contains the fields Additive1ID, Additive2ID, Additive3ID and Weight%1, Weight%2, and Weight%3, where the three AdditiveIDs are foreign keys that link to the Additive table. I have a query called combine additives with a field containing the expression I’m trying to build.

Starting with my original expression:

[Weight%1]*100 & ”% “ & [Additive1ID] & " " & [Weight%2]*100 & ”% “ & [Additive2ID] & " " & [Weight%3]*100 & ”% “ & [Additive3ID]

I’ve replaced any instance of AdditiveXID (where X is 1, 2, or 3) with:

DLookUp("[AdditiveName]","Additives","[ID] = Tables![All Samples]![AdditiveXID]")

When I run the query I get an error message that says,

The expression you entered as a query parameter produced this error: ‘Microsoft Office Access can't find the name ‘Tables!All Samples!Additive1ID’ you entered in the expression’

So what am I doing wrong?

PS Since the AdditiveXID fields in my All Samples table are 2 column comboboxes (the first column is ID (it’s hidden) and the second is AdditiveName, both fields from my additive table. I also tried replacing AdditiveXID with AdditiveXID.Column(1) but that resulted in an error:

Undefined function ‘Additive1ID.Column’ in expression.

What was the problem with what I did there?

[sshepard]

I'm still looking for help. Would anyone mind coming to my rescue here?

[sshepard]

Of course, as soon as I bump my post, I figure out the answer. For inquiring minds that want to know:
If you want to call up a value in a form or report, etc. you have to precede the name of the form/report with Forms! or Reports! in the DLookup expression. Apparently this is not necessary for tables. Thus, by removing Tables! the function now works.