Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 692
  • Last Modified:

What does it mean this access query

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
jaisonshereen
Asked:
jaisonshereen
  • 5
  • 5
  • 2
2 Solutions
 
jamesguCommented:
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
 
jaisonshereenAuthor Commented:
is this assigning values 0 or 1 to a table?
0
 
aikimarkCommented:
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jamesguCommented:
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
 
jaisonshereenAuthor Commented:
what will be this 0 and 1 is used for?
0
 
aikimarkCommented:
since you haven't shown us the entire SQL statement and the code that invokes the SQL, we don't know.
0
 
jaisonshereenAuthor Commented:
this is the query
T-001-SearchCriteria.jpg
0
 
aikimarkCommented:
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
 
jaisonshereenAuthor Commented:
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;

Open in new window

0
 
aikimarkCommented:
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])

Open in new window

0
 
aikimarkCommented:
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; 

Open in new window

0
 
jaisonshereenAuthor Commented:
Thanks a lot!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now