Prev: Help! Unmatched Data Query
Next: Appned Query Performance
From: John Spencer on 6 May 2010 09:06
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!
First  |  Prev  | 
Pages: 1 2
Prev: Help! Unmatched Data Query
Next: Appned Query Performance