IF function in Query

  • Thread starter Thread starter Supe
  • Start date Start date
S

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"," "))))
 
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"," "))))
 
That did it. Thank you very much.


KARL DEWEY said:
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


Supe said:
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"," "))))
Click to expand...
Click to expand...
 
Back
Top