IF function in Query

[Supe]

Can an IF function be used in a Query? I set up the IF function below to use
in an Excel worksheet to list the Banner name by searching for a name within
the store name field. My plan was to export the query into an Excel sheet,
but it exceeds the maximum number of lines allowed(Excel 2003). Can this IF
function be set up in the query so that it can determine the banner in the
query?


=IF(COUNTIF(A3,"*Shopper*"),"Shoppers",IF(COUNTIF(A3,"*Cub*"),"Cub",IF(COUNTIF(A3,"*Farm Fresh*"),"Farm Fresh",IF(COUNTIF(A3,"*Bigg*"),"Biggs"," "))))

 

[KARL DEWEY]

Looks like you want nested IIF statement but have wrong syntax.
IIF([YourFieldName] test, True answer, False answer)

Try this --
IIF([YourField] Like "*Shopper*","Shoppers", IIF([YourField] Like
"*Cub*","Cub", IIF([YourField] Like "*Farm Fresh*","Farm Fresh",
IIF([YourField] Like "*Bigg*","Biggs"," "))))

 

[Supe]

That did it. Thank you very much.

Looks like you want nested IIF statement but have wrong syntax.
IIF([YourFieldName] test, True answer, False answer)

Try this --
IIF([YourField] Like "*Shopper*","Shoppers", IIF([YourField] Like
"*Cub*","Cub", IIF([YourField] Like "*Farm Fresh*","Farm Fresh",
IIF([YourField] Like "*Bigg*","Biggs"," "))))

--
KARL DEWEY
Build a little - Test a little

Can an IF function be used in a Query? I set up the IF function below to use
in an Excel worksheet to list the Banner name by searching for a name within
the store name field. My plan was to export the query into an Excel sheet,
but it exceeds the maximum number of lines allowed(Excel 2003). Can this IF
function be set up in the query so that it can determine the banner in the
query?


=IF(COUNTIF(A3,"*Shopper*"),"Shoppers",IF(COUNTIF(A3,"*Cub*"),"Cub",IF(COUNTIF(A3,"*Farm Fresh*"),"Farm Fresh",IF(COUNTIF(A3,"*Bigg*"),"Biggs"," "))))