SQL IIF group by result in MS Access 2007

I am using some SQL which I would have thought should work. However I am fast learning that MS Access has it's own idea of how to use SQL.

SELECT count([system_info].[id]) as CountofID,
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 512000, "<512mb", 
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 1000000, "512mb - 1GB", "nope")) as MemoryText
FROM system_info
GROUP BY MemoryTe开发者_开发技巧xt;

So the above code gives me the error message "You tried to execute a query which does not include the specified expression XXX as part of an aggregate function".

I've googled a fair bit and am still pretty stuck. If I remove the count AND the groupBY, the query will run and I see a list of the labels I create for "MemoryText".

Thanks for any help.

---

If I remove the count AND the groupBY, the query will run and I see a list of the labels I create for "MemoryText".

In that case, save the version which works as qryBase:

SELECT [system_info].[id]),
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 512000, "<512mb", 
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 1000000, "512mb - 1GB", "nope")) as MemoryText
FROM system_info;

Then your aggregate query can be:

SELECT MemoryText, count(id) as CountofID
FROM qryBase
GROUP BY MemoryText;

Edit: However, this expression puzzles me:

Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 512000

Instead of 512000, I would have expected 524288 (512 * 1024).

And the output from Left() will be String, so your expression compares a string to a number. Although the database engine may cast the string to number before the comparison, I would do it explicitly.

Val(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)) < 524288

You may get better suggestions if you tell us the data type of your CSMemory field and show us some sample values.

Edit2: From Access' Help: The Val function stops reading the string at the first character it can't recognize as part of a number.

So Val("2052644 KB") would ignore the non-digit characters and give you the number 2052644. However, your sample values also include commas, so Val() would stop reading at the first comma ... Val("2,052,644 KB") would give you 2.

If your query is run within an Access session with Access 2000 or newer, you could use the Replace() function to strip out the commas (replace commas with zero-length strings) before evaluating the text with Val(). In the Immediate Window:

? Replace("2,052,644 KB", ",", "")
2052644 KB

? Val(Replace("2,052,644 KB", ",", ""))
 2052644 

So instead of using Left() and Len(), you could use:

Val(Replace(CSMemory, ",", ""))  < 512000

---

You need to put the whole of the Memory Text expression in the GROUP BY statement

SELECT count([system_info].[id]) as CountofID,
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 512000, "<512mb", 
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 1000000, "512mb - 1GB", "nope")) as MemoryText
FROM system_info
GROUP BY IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 512000,
"<512mb", 
IIF(Left([system_info].[CSMemory],Len([system_info].[CSMemory])-3)  < 1000000, "512mb - 1GB", "nope"))