Conditionally select data in a SELECT statement

[SueBwork]

I am trying to conditionally select information from a table to create input for a Merge-Mail document. The output is an award certificate. Sometimes I want to insert some text at a specific location and sometimes I want to insert nothing, depending on a variable/field selected from another table (in the same SELECT statement).

Here is the select statement (before my "fix" that didn't work):

strSelect = "SELECT tblDogTitles.dogtitleID, tblDogTitles.dogregnbr, tblDogTitles.processeddt, Day(tblTrials.trialdt) & ' ' & MonthName(Month(tblTrials.trialdt)) & ' ' & Year(tblTrials.trialdt) AS outtrialdt, tblTitles.title, tblTitles.titleabbrev, tblDogs.formalname, tblPeople.fname & (' '+tblPeople.midinit+'.') & ' ' & tblPeople.lname & (' '+tblPeople.suffix+'.') AS owner, tblPeople.altperson AS altowner, tblClasses.class & ' Class' AS classtxt, tblUserSettings.uval FROM tblUserSettings, tblTrials INNER JOIN (((tblClasses INNER JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) INNER JOIN tblTitles ON tblClasses.classID = tblTitles.classID) INNER JOIN (tblPeople INNER JOIN (tblDogs INNER JOIN tblDogTitles ON tblDogs.dogregnbr = tblDogTitles.dogregnbr) ON tblPeople.peopleID = tblDogs.peopleID) ON (tblTrialClass.trialclassID = tblDogTitles.trialclassID) AND (tblTitles.titleID = tblDogTitles.titleID)) ON tblTrials.trialID = tblTrialClass.trialID "

Here is my attempt at a fix, which didn't work:

strSELECT = "SELECT tblDogTitles.dogtitleID, tblDogTitles.dogregnbr, tblDogTitles.processeddt, Day(tblTrials.trialdt) & ' ' & MonthName(Month(tblTrials.trialdt)) & ' ' & Year(tblTrials.trialdt) AS outtrialdt, tblTitles.title, tblTitles.titleabbrev, tblDogs.formalname, tblPeople.fname & (' '+tblPeople.midinit+'.') & ' ' & tblPeople.lname & (' '+tblPeople.suffix+'.') AS owner, tblPeople.altperson AS altowner, IIF(titleabbrev = 'UDX-H' OR titleabbrev = 'OTCH-H','' AS classtxt,tblClasses.class & ' Class' AS classtxt), tblUserSettings.uval FROM tblUserSettings, tblTrials INNER JOIN (((tblClasses INNER JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) INNER JOIN tblTitles ON tblClasses.classID = tblTitles.classID) INNER JOIN (tblPeople INNER JOIN (tblDogs INNER JOIN tblDogTitles ON tblDogs.dogregnbr = tblDogTitles.dogregnbr) ON tblPeople.peopleID = tblDogs.peopleID) ON (tblTrialClass.trialclassID = tblDogTitles.trialclassID) AND (tblTitles.titleID = tblDogTitles.titleID)) ON tblTrials.trialID = tblTrialClass.trialID "

Any ideas on how to get what I want?

Regards,
SueB

 

[FoFa]

Try:
IIF(titleabbrev = 'UDX-H' OR titleabbrev = 'OTCH-H','',tblClasses.class & ' Class') AS classtxt,

 

[SueBwork]

Thank you for the correction.
SueB