|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 8th, 2008, 10:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
IF function in Query
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"," ")))) 
|
|
#2
May 8th, 2008, 10:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IF function in Query
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" wrote: 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"," "))))
|
|
#3
May 9th, 2008, 01:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IF function in Query
That did it. Thank you very much.
"KARL DEWEY" wrote: 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" wrote: 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"," "))))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
