Re: IIf Statement in ADP

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ron Weiner (weinNoSpam1_at_mindspring.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 16:40:24 -0500

Yup!

You need to substuite Access's IIF() with SQL's much more versitle CASE()
function

SELECT CASE [tblEmployee].[Terminated]
    WHEN -1 THEN 'X'
    ELSE ''
END AS Terminated

Above is but one example for using CASE() Take a look at Sql's BOL for more
info.

Ron W

"Dan M" <dannerinoremove@aol.com> wrote in message
news:140e001c3f7ce$842e3410$a501280a@phx.gbl...
> It would appear that using an IIF statement as part of the
> SELECT statement no longer works and is no longer a
> recognized function. Is there a workaround?
>
> In Access 97, I relied on IIF in many list boxes, to
> display the contents of a Yes/No field in a more
> meaningful way. My column would be selected as...
> SELECT IIF([tblEmployee].[Terminated] = -1, "X", "") AS
> Terminated
>
> It displayed a column titled Terminated and displayed an X
> for every employee whose Terminated field was set to true,
> and displayed nothing otherwise. Migrating to XP, using
> an ADP with SQL Server back end, this function seems to be
> illegal. Oh please, tell me there's a way to manufacture
> columns on the fly in a SQL statement.

Relevant Pages

  • Re: Change default return string
    ... You need to understand what the IIF statement is doing in the Select ... IIF(expression, resultIfTrue, resultIfFalse) ... That's why I recommended my alternative where you preserve the Boolean nature of the field and display it any way you want to ... | "Stefan B Rusynko" wrote: ...
    (microsoft.public.frontpage.programming)
  • Re: Return text based on other field values
    ... You need to use a nested IIf statement. ... I want to display a control that contains the current status (Received, ... So the query needs to look at rcvd, if contains an entry it will ... unless strtd contains an entry in which case it will ...
    (microsoft.public.access.queries)
  • Re: Change value of field to 0 if specific condition exists on other f
    ... I don't think you want to use IIF in the criteria. ... Let me ask, you want all records in your table to display in your query, ... sum of two fields that will default the sum result to zero if the item ...
    (microsoft.public.access.queries)
  • Re: IIf statement in a report
    ... I'm not sure why you would return a text value "0" from your IIf() ... > My goal is to have a text box display the hours of overtime each employee ... > text box title "txtOvertimeHours" and inserted this iif statement in the ... > How can I fix the statement so that a zero is displayed for all totals 40 ...
    (microsoft.public.access.reports)
  • Re: Filter - Repost
    ... I'll start by commenting that the IIF() is not the best technique. ... > I have a report based on the below SQL; is there any way to display just ... what I mean is for example today I got 4 Invalid MI; ...
    (microsoft.public.access.queries)