If the linkage to TES table is based on Badge in some cases and on BadgeET in
other cases, then the solution would involve nested queries.

OR if you only need the one set of data you could use another expression
(Dlookup) or a subquery.

Field: DLookUp("LastName & "", "" & FirstName","TES",
"Bdg=""" & IIF([Test Stats].TestType="Eng",
[Project].[BadgeET],[Project].[Badge]) & """")

OR using correlated subquery

Field: TheName: (SELECT First(LastName & ", " & FirstName) FROM Tes WHERE Bdg
= IIF([Test Stats].TestType="Eng", [Project].[BadgeET],[Project].[Badge]))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

James wrote:
Great, that worked beautifully! I ended up using IIF([Test
Stats].TestType="Eng",[Project].[BadgeET],[Project].[Badge]). Any other
condition would default to display [Project].Badge (instead of null by using
the nested IIF)

To add to this, now that I have either Badge or BadgeET, how can I use that
to display other information. ie.....

If TestType = "Eng" then
Display TES.LastName & ", " TES.FirstName Where TES.Bdg =
Project.Badge
ElseIf TestType = "Pkg" then
Display TES.LastName & ", " TES.FirstName Where TES.Bdg =
Project.Badge
End if

TES is the name of another table with fields named Bdg, FirstName, and
LastName so i should be able to use the "badge" to locate the name....

Thanks again!