If statement not working

[gkaro]

Hi all,

new to this so this may seems a little elementary Im sure.
Trying to figure out how to perform the following

Table includes
YEAR, WEEK, ITEM, SALES
I need a field that gives me something if SALES are less that 50


I can get it to work with Expr1: IIf([SALES]<50,"UNDER 50",Null)

but it doesnt work if I have sums on the original table.
Sometimes I need to delete WEEK and have the total SALES for the whole year as gouped by and SUM.

The Expr1: IIf([SALES]<50,"UNDER 50",Null) doesnt work on the final calculated number.

thanks

[pbaldy]

Does this do what you want?

IIf(Sum([SALES])<50,"UNDER 50",Null)

[TheShabz]

Try replacing the Null in your second IIf with "". I don't think you can set a value to Null.

On another note, try to use something other than YEAR and WEEK as your field names. YEAR is a reserved word in Access, and while WEEK isn't, it's still not "proper."

[gkaro]

It doesnt Work.

Get message

Cannot have aggregate function in GROUP BY Clause IIf(Sum([SALES])<50,"UNDER 50",Null)

[ssanfu]

It doesnt Work.

Get message

Cannot have aggregate function in GROUP BY Clause IIf(Sum([SALES])<50,"UNDER 50",Null)

Will you pPost the SQL of your query?

You might try changing the "Group By" to "Expression".

[gkaro]

SELECT COSTCO.YEAR, COSTCO.[DYSON SKU], Sum(COSTCO.SALES) AS SumOfSALES, IIf(Sum([SALES])<50,"UNDER 50","") AS Expr1
FROM COSTCO
GROUP BY COSTCO.YEAR, COSTCO.[DYSON SKU], IIf(Sum([SALES])<50,"UNDER 50","")
HAVING (((COSTCO.YEAR)=2012));

[gkaro]

HEY THANKS..That worked.
Needed to change Group by to Expression. Don't understand when and whats the difference.
Any help would be great!

[ssanfu]

HEY THANKS..That worked.
Needed to change Group by to Expression. Don't understand when and whats the difference.
Any help would be great!

Don't know if I can explain it right. Took me a while to (somewhat) understand it.

Look at this line:

GROUP BY COSTCO.YEAR, COSTCO.[DYSON SKU], IIf(Sum([SALES])<50,"UNDER 50","")

"Year" is a field in the query (Bad choice - it is a reserved word).
"[DYSON SKU]" is a field in the query (spaces are also bad). The query "knows" it can gather the records together by these two fields. But what about "IIf(Sum([SALES])<50,"UNDER 50","")"? It is not a field, it is a formula - aka "Expression". You cannot group by an expression.

In a Totals Query, if you have an expression (formula/calculation), you have to change "Group By" to "Expression".

Clear as mud????

[gkaro]

I guess that makes sense. Have a question about the reserved field [YEAR]. I havent come across any issues but more than one person is telling me this is wrong. Also for spaces.
I have 3million lines of data and run queries every day. Have not encountered anything wrong.
Is there something that I am missing. Possibly slower query times? Inaccurate results??
thanks

[ssanfu]

I guess that makes sense. Have a question about the reserved field [YEAR]. I havent come across any issues but more than one person is telling me this is wrong. Also for spaces.
I have 3million lines of data and run queries every day. Have not encountered anything wrong.
Is there something that I am missing. Possibly slower query times? Inaccurate results??
thanks

Things like spaces in object names and special characters will cause problems if you ever decide you need better security or you reach Access's size limit and you want to convert to Oracle, MySQL, or SQL Server.

Reserved words list is found at Allen Browne's site:
http://allenbrowne.com/AppIssueBadWord.html

Using reserved words can cause Access to become confused. I had strange problems in an early mdb because I used "Date" as a field name. I had to use brackets to get the code to run. I finally added a prefix, modified the forms, reports, queries and code to eliminate the problem. Now I use a naming convention (stolen form a friend) and no longer have issues due to object names. Other issues, yes, but not because of names.

Plus, it got confusing because "Date" is not descriptive. "Date" of what???

My 2 cents....

[TheShabz]

for example, if you write
SELECT Year from MyTable WHERE myID = 1
assuming all data exists, the SQL should error out because Access is expecting you to use the Year() function. it also has reserved special characters, *, /, +, etc. To prevent confusion, you would have to place [ ] around every table and field name to force Access to read it as a table or field name. However, that's pretty tedious so we just avoid using the reserved words. Ideal solutions are to either be more descriptive, or use the abbreviated datatype before the name, like dtDate or intID or strName.