Solved

# What does it mean this access query

Posted on 2008-10-20
682 Views
IIf([T_FPtr_GeneralSearch].[PromoPeriod]="Period",1,IIf([T_FPtr_GeneralSearch].[PromoPeriod]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period],1,0))
0
Question by:jaisonshereen
• 5
• 5
• 2

LVL 9

Accepted Solution

jamesgu earned 250 total points
ID: 22760333
IIf([T_FPtr_GeneralSearch].[PromoPeriod]="Period",1,

IIf([T_FPtr_GeneralSearch].[PromoPeriod]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period],1,0))

if T_FPtr_GeneralSearch].[PromoPeriod]="Period, the value is 1
else if T_FPtr_GeneralSearch].[PromoPeriod]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period]
else the vlaue is 0

0

Author Comment

ID: 22760429
is this assigning values 0 or 1 to a table?
0

LVL 45

Expert Comment

ID: 22760560
As VB code (see snippet)

Note: your SQL would be easier to understand if you used aliases for your tables.
``````If T_FPtr_GeneralSearch].[PromoPeriod] = "Period" Then

Value = 1

ElseIf T_FPtr_GeneralSearch].[PromoPeriod] = T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period] Then

Value = 1

Else

Value = 0

End If
``````
0

LVL 9

Expert Comment

ID: 22760617
it's either 0 or 1 based on the current  value of

T_FPtr_GeneralSearch].[PromoPeriod]

T_FPtr_GeneralSearch].[PromoPeriod]
and [T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period]

if T_FPtr_GeneralSearch].[PromoPeriod] = "Period' or
T_FPtr_GeneralSearch].[PromoPeriod] = [T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period], then the value of the statement is 1,
0 otherwise
0

Author Comment

ID: 22773567
what will be this 0 and 1 is used for?
0

LVL 45

Expert Comment

ID: 22773578
since you haven't shown us the entire SQL statement and the code that invokes the SQL, we don't know.
0

Author Comment

ID: 22773588
this is the query
T-001-SearchCriteria.jpg
0

LVL 45

Expert Comment

ID: 22776492
I asked to see the SQL, not a screen shot of the query builder.

Also, what about the code that invokes it?  If you need to know HOW the 0/1 value is used, it can only be answered by examining the invoking/consuming routines.  For all I know, these values might be bound to a checkbox control somewhere.
0

Author Comment

ID: 22776538
See this
``````SELECT T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Temp_PromoID AS Temp_PromoID

FROM T_FPtr_GeneralSearch, T_100_q010_PromoTitleandDetailsAsOne_SELECT

WHERE (((IIf([T_FPtr_GeneralSearch].[status]="Status",1,IIf([T_FPtr_GeneralSearch].[Status]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[proceed],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[PromoPeriod]="Period",1,IIf([T_FPtr_GeneralSearch].[PromoPeriod]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[location]="location",1,IIf([T_FPtr_GeneralSearch].[location]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[location],1,0)))=1) AND ((IIf(Val([T_FPtr_GeneralSearch].[Year])=0,1,IIf(Val([T_FPtr_GeneralSearch].[year])=([T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[year]),1,0)))=1) AND ((IIf(([T_FPtr_GeneralSearch].[StartDate])="Start Date",1,IIf(DateValue([T_FPtr_GeneralSearch].[StartDate])=([T_100_q010_PromoTitleandDetailsAsOne_SELECT].[PPStartDate]),1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[TempPromoID]=0,1,IIf([T_FPtr_GeneralSearch].[TempPromoID]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[Temp_PromoID],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[CatID]=0,1,IIf([T_FPtr_GeneralSearch].[CatID]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[CategoryID],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[CategoryMan]="Category Manager",1,IIf([T_FPtr_GeneralSearch].[CategoryMan]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[Category_Man],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[EnteredBy]="Log on",1,IIf([T_FPtr_GeneralSearch].[EnteredBy]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[EnteredBy],1,0)))=1) AND ((IIf([T_FPtr_GeneralSearch].[Article]=0,1,IIf([T_FPtr_GeneralSearch].[Article]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[Article],1,0)))=1))

GROUP BY T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Temp_PromoID;
``````
0

LVL 45

Expert Comment

ID: 22777069
Within the Where clause, the 0 and 1 values are used as a replacement for a Boolean OR operator.  The Where clause should be written as shown in the snippet.

``````WHERE ([GenSrch].[status]="Status" OR [GenSrch].[Status]=[T_100].[proceed])

AND ([GenSrch].[PromoPeriod]="Period" OR [GenSrch].[PromoPeriod]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period])

AND ([GenSrch].[location]="location" OR [GenSrch].[location]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[location])

AND (Val([GenSrch].[Year])=0 OR Val([GenSrch].[year])=([T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[year])

AND ([GenSrch].[StartDate])="Start Date" OR DateValue([GenSrch].[StartDate])=([T_100].[PPStartDate])

AND ([GenSrch].[TempPromoID]=0 OR [GenSrch].[TempPromoID]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[Temp_PromoID])

AND ([GenSrch].[CatID]=0 OR [GenSrch].[CatID]=[T_100].[CategoryID])

AND ([GenSrch].[CategoryMan]="Category Manager" OR [GenSrch].[CategoryMan]=[T_100].[Category_Man])

AND ([GenSrch].[EnteredBy]="Log on" OR [GenSrch].[EnteredBy]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[EnteredBy])

AND ([GenSrch].[Article]=0 OR [GenSrch].[Article]=[T_100].[Article])
``````
0

LVL 45

Assisted Solution

aikimark earned 250 total points
ID: 22777372
Let me show all the SQL, since I aliased the tables
``````SELECT T_100.T_OUTPUT_PromoChosenAtEntry_MAIN.Temp_PromoID AS Temp_PromoID

FROM T_FPtr_GeneralSearch As GenSrch, T_100_q010_PromoTitleandDetailsAsOne_SELECT As T_100

WHERE ([GenSrch].[status]="Status" OR [GenSrch].[Status]=[T_100].[proceed])

AND ([GenSrch].[PromoPeriod]="Period" OR [GenSrch].[PromoPeriod]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period])

AND ([GenSrch].[location]="location" OR [GenSrch].[location]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[location])

AND (Val([GenSrch].[Year])=0 OR Val([GenSrch].[year])=([T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[year])

AND ([GenSrch].[StartDate])="Start Date" OR DateValue([GenSrch].[StartDate])=([T_100].[PPStartDate])

AND ([GenSrch].[TempPromoID]=0 OR [GenSrch].[TempPromoID]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[Temp_PromoID])

AND ([GenSrch].[CatID]=0 OR [GenSrch].[CatID]=[T_100].[CategoryID])

AND ([GenSrch].[CategoryMan]="Category Manager" OR [GenSrch].[CategoryMan]=[T_100].[Category_Man])

AND ([GenSrch].[EnteredBy]="Log on" OR [GenSrch].[EnteredBy]=[T_100].[T_OUTPUT_PromoChosenAtEntry_MAIN].[EnteredBy])

AND ([GenSrch].[Article]=0 OR [GenSrch].[Article]=[T_100].[Article])

GROUP BY T_100.T_OUTPUT_PromoChosenAtEntry_MAIN.Temp_PromoID;
``````
0

Author Closing Comment

ID: 31507923
Thanks a lot!
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

#### 762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!