SQL IIF group by result in MS Access 2007 -

-

i using sql have thought should work. fast learning ms access has it's own idea of how use sql. 

select count([system_info].[id]) 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")) memorytext system_info group memorytext;

so above code gives me error message "you tried execute query not include specified expression xxx part of aggregate function".

i've googled fair bit , still pretty stuck. if remove count , groupby, query run , see list of labels create "memorytext".

thanks help.

if remove count , groupby, query run , see list of labels create "memorytext".

in case, save version works 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")) memorytext system_info;

then aggregate query can be:

select memorytext, count(id) countofid qrybase group memorytext;

edit: however, expression puzzles me:

left([system_info].[csmemory],len([system_info].[csmemory])-3)  < 512000

instead of 512000, have expected 524288 (512 * 1024).

and output left() string, expression compares string number. although database engine may cast string number before comparison, explicitly.

val(left([system_info].[csmemory],len([system_info].[csmemory])-3)) < 524288

you may better suggestions if tell data type of csmemory field , show sample values.

edit2: access' help: the val function stops reading string @ first character can't recognize part of number.

so val("2052644 kb") ignore non-digit characters , give number 2052644. however, sample values include commas, val() stop reading @ first comma ... val("2,052,644 kb") give 2.

if query run within access session access 2000 or newer, use replace() function strip out commas (replace commas zero-length strings) before evaluating text val(). in immediate window:

? replace("2,052,644 kb", ",", "") 2052644 kb  ? val(replace("2,052,644 kb", ",", ""))  2052644

so instead of using left() , len(), use:

val(replace(csmemory, ",", ""))  < 512000

Comments

Post a Comment

Popular posts from this blog

javascript - Enclosure Memory Copies -

-
function myclass() { //lots , lots of vars , code here. this.bar = function() { //do numerous enclosed myclass vars } this.foo = function() { alert('hello'); //don't enclosed variables. } } each instance of myclass gets own copy of bar , foo, why prototyped methods use less memory. however, know more memory use of inner methods. it seems obvious me (1) below must true. agree? not distinct myclass instances have own distinct copies of bar, must have own distinct copies of myclass enclosure. (or else how bar method keep myclass variables straight on per instance basis?) now (2) question i'm after. since inner foo method doesn't use in myclass enclosure natural qustion is: javascript smart enough not keep myclass enclosure in memory use of foo? this depend entirely on implementation not language. naive implementations keep far more around others. answer true e.g. v8 (chrome's js engine) may not true spider...
Read more

php - Replacing tags in braces, even nested tags, with regex -

-
example preg_replace('/\{[a-za-z.,\(\)0-9]+\}/', 'replaced', 'lorem ipsum dolor sit {tag1({tag2()})}, consectetur adipiscing elit.'); the result: lorem ipsum dolor sit {tag1(replaced)}, consectetur adipiscing elit. question as can see "tag2" has been replaced, want replace "tag1" know how can this? (in cases might this: {tag1({tag2({tag3()})})}) , on.) btw using preg_replace_callback, easier show preg_replace here site can test code: http://www.spaweditor.com/scripts/regex/index.php you need add curly braces character set. here's pattern used: /\{[a-za-z.,\(\)\{\}0-9]+\}/ and here result: "lorem ipsum dolor sit replaced, consectetur adipiscing elit."
Read more