Conditionally select fields in SQL statement. Possible?

[SueB]

I currently have a 'mail-merge' process in my Access db project. It
generates custom filled out Award Certificates based on an SQL SELECT
statement in a VBA routine invoked by clicking on a command button.

The "problem": I want to conditionally insert some text into the award
certificate based on a field selected by the SELECT statement. Is this
possible?

Details:
One of the fields selected is a concatenation of a value from a table
(class from tblClasses with values of "Novice", "Open", or "Utility")
and the text " Class". The end text is "Novice Class" or "Open Class"
or "Utility Class". However, there are certain award certificates that
should not get this text inserted, when the award is for a unique
situation.

it looks like (in the SQL statement):
"SELECT ....., ([tblClasses].[class] + ' Class') as classtxt, ..."

The inserted field (into the merge template) is classtxt.
Sometimes, however, I don't want to insert anything for classtxt. I can
"gate" this based on another selected field in the SQL statement,
[tblTitles].[titleabbrev].
Is it possible to conditionally populate a selected field in an SELECT
statement and then if the field is not selected then don't populate
(insert) the data in the merged document?
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On Mon, 26 Sep 2005 16:56:59 GMT, SueB <sl*****@verizon.net> wrote:

Yes. You could use the IIf function or the Choose function. Here is an
example that would work in the Northwind sample application:
select 'This customer lives in ' & iif(State='TX', 'the Greatest
State', 'just another state') from Customers

Btw, note how I'm using the official & sign to concatenate strings.
Better than + in some situations.

-Tom.

I currently have a 'mail-merge' process in my Access db project. It
generates custom filled out Award Certificates based on an SQL SELECT
statement in a VBA routine invoked by clicking on a command button.

The "problem": I want to conditionally insert some text into the award
certificate based on a field selected by the SELECT statement. Is this
possible?

Details:
One of the fields selected is a concatenation of a value from a table
(class from tblClasses with values of "Novice", "Open", or "Utility")
and the text " Class". The end text is "Novice Class" or "Open Class"
or "Utility Class". However, there are certain award certificates that
should not get this text inserted, when the award is for a unique
situation.

it looks like (in the SQL statement):
"SELECT ....., ([tblClasses].[class] + ' Class') as classtxt, ..."

The inserted field (into the merge template) is classtxt.
Sometimes, however, I don't want to insert anything for classtxt. I can
"gate" this based on another selected field in the SQL statement,
[tblTitles].[titleabbrev].
Is it possible to conditionally populate a selected field in an SELECT
statement and then if the field is not selected then don't populate
(insert) the data in the merged document?
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

Tom,

Wow. A really simple solution. Thanks.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[SueB]

Tom,

Well I guess it's not as simple as I thought. Here is the SELECT
statement that I have to work with. The field/variable that I need to
setup is "classtxt". You will see that currently I am selecting
[tblClasses].[class] and appending the text " Class" to the data. But
in some instances I want to set "classtxt" to a single blank (" ") or ""
(either one ... basically no text).

This way sometimes the merged document will insert some text and
sometimes it will not insert some text (depending on the value of
another variable in the SELECT statement (tblTitles.titleabbrev).

Here is the original SELECT statement followed by the updated SELECT
statement that does not work.
Do you have any ideas?

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 "
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 "

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Jana]

Sue:

Your line with the IIF should be like this IIF(test,
trueresult,falseresult) AS variablename. Don't put the AS classtext
within the IIF. Corrected, it should look like this:

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

That should do it!

Jana

[SueB]

Jana,

Thanks, so much.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Mike Preston]

On Thu, 29 Sep 2005 17:15:18 GMT, SueB <sl*****@verizon.net> wrote:

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

Replace the above with:

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

mike

[Mike Preston]

On 29 Sep 2005 10:50:44 -0700, "Jana" <Ba********@gmail.com> wrote:

Sue:

Your line with the IIF should be like this IIF(test,
trueresult,falseresult) AS variablename. Don't put the AS classtext
within the IIF. Corrected, it should look like this:

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

That should do it!

Jana,

Did this message thread properly for you? It would save people a lot
of work if all responses properly thread. I'm just wondering if it
threaded on your newsreader?

mike

[Jana]

Mike--sorry, I don't know what you mean by 'threaded properly'. My
post showed up in Google Groups just fine...

Jana

[Mike Preston]

On 30 Sep 2005 10:14:19 -0700, "Jana" <Ba********@gmail.com> wrote:

Mike--sorry, I don't know what you mean by 'threaded properly'. My
post showed up in Google Groups just fine...

Interesting. I'm using FreeAgent, and your response started a new
thread. I read the "other" thread and noticed that there weren't any
responses, so I responded. Had I seen your response, I wouldn't have.
Did you post from google?

mike

[Jana]

Mike:

Yes, I posted from Google. Everything seems fine here...

Jana