Only show numeric values

[dr4ke]

I have a query which does some mathematical calculations on my data. Some of the results are Text ('No Change' & 'Used Space Decreased') and the rest are all numerical values. Is there a way to pull out only the entries which have a numeric value bigger than 0?



I've tried adding 'WHERE [TableName].[TimeLeft] >0' but it doesn't work.

thoughts?

 SELECT [7LUNQuery].SANName, [7LUNQuery].AggregateName, [7LUNQuery].LUNName, Int(Round(([7LUNQuery].FirstUsedAmount)/1024^2,0)) AS FirstUsedAmountGB, Int(Round(([7LUNQuery].LastUsedAmount)/1024^2,0)) AS LastUsedAmountGB, Int(Round(([7LUNQuery].FirstTotalAmount)/1024^2,0)) AS FirstTotalAmountGB, Int(Round(([7LUNQuery].LastTotalAmount)/1024^2,0)) AS LastTotalAmountGB, FirstTotalAmountGB-FirstUsedAmountGB AS FirstFreeAmountGB, LastTotalAmountGB-LastUsedAmountGB AS LastFreeAmountGB, Int(Round((FirstUsedAmountGB/FirstTotalAmountGB)*100,0)) AS FirstPercentageAmount, Int(Round((LastUsedAmountGB/LastTotalAmountGB)*100,0)) AS LastPercentageAmount, LastUsedAmountGB-FirstUsedAmountGB AS DifferenceUsedAmountGB, DateDiff("d",(Forms![FrontPage]![SelectStart]),(Forms![FrontPage]![SelectEnd])) AS DateDifference, Int(Round((DifferenceUsedAmountGB/DateDifference),0)) AS DailyIncreaseAmountGB, IIf([DailyIncreaseAmountGB]=0,'No Change',IIf([DailyIncreaseAmountGB]<0,'Used Space Decreased',Int(Round(LastFreeAmountGB/IIf(DailyIncreaseAmountGB=0,'Null',DailyIncreaseAmountGB),0)))) AS TimeLeft
FROM 7LUNQuery;

[June7]

Why doesn't it work - error message, wrong results, nothing happens?

Numeral characters sort before alpha characters in ascending alpha/numeric sort. So numeral strings will be < any alpha.

TimeLeft is not a field in table, it is constructed by expression in query. Cannot reference constructed field in WHERE clause of same query. Must repeat the expression as the criteria. The TimeLeft expression is based on other constructed fields so still might fail.

Suggest you build another query based on first query.
Result of TimeLeft expression is either a text string or number value. I expect you will get 'data type mismatch' error with > 0 criteria.
In second query, create field with expression: Val(TimeLeft). Criteria would be: > 0

[dr4ke]

Hi!

Sorry, yes that was the error message ('Data type mismatch'). I don't fully understand what you've said above, but I created another query and it works so I'm happy :-)

Thanks,
Dr4ke