Using Min() and Max() on column with both strings and numbers - can it work?

[Jesper F]

I have to tables I need output from with som min and max values.
However, the column in the table contains both strings and numbers and I
need MIN and MAX of the numbers off course.

This works is there there are only numbers in the tables:

SELECT table1.varID, table1.varname,
Min(table2.field1) AS Minfield1,
Max(table2.field1) AS Maxfield1,
FROM table1
LEFT JOIN table2
ON table1.varID=table2.varID
GROUP BY table1.varID, table1.varname

But there ARE strings in the column and I'm trying with this:

SELECT table1.varID, table1.varname,
IIF(isnumeric([field1]),Min(table2.field1),'') AS Minfield1,
IIF(isnumeric([field1]),Max(table2.field1),'') AS Maxfield1,
FROM table1
LEFT JOIN table2
ON table1.varID=table2.varID
GROUP BY table1.varID, table1.varname

But I'm getting the error: "you've tried to run a query that doesn't include
IIF(isnumeric([field1]),Min(table2.field1),'') as part of an aggregate
function.".

Can this work? Can I get MIN and MAX values from a column even though
there're strings in the the column?

Thanks.


Jesper

 

[John Nurick]

Hi Jesper,

You need to run the Max() and Min() on an expression that returns the
number (if the value is numeric) or Null (if it isn't). E.g.:

SELECT
Max(IIF(IsNumeric(Nz([F1], "")), CDbl([F1]), Null)) As Biggest,
Min(IIF(IsNumeric(Nz([F1], "")), CDbl([F1]), Null)) As Smallest
FROM TheTable;

I have to tables I need output from with som min and max values.
However, the column in the table contains both strings and numbers and I
need MIN and MAX of the numbers off course.

This works is there there are only numbers in the tables:

SELECT table1.varID, table1.varname,
Min(table2.field1) AS Minfield1,
Max(table2.field1) AS Maxfield1,
FROM table1
LEFT JOIN table2
ON table1.varID=table2.varID
GROUP BY table1.varID, table1.varname

But there ARE strings in the column and I'm trying with this:

SELECT table1.varID, table1.varname,
IIF(isnumeric([field1]),Min(table2.field1),'') AS Minfield1,
IIF(isnumeric([field1]),Max(table2.field1),'') AS Maxfield1,
FROM table1
LEFT JOIN table2
ON table1.varID=table2.varID
GROUP BY table1.varID, table1.varname

But I'm getting the error: "you've tried to run a query that doesn't include
IIF(isnumeric([field1]),Min(table2.field1),'') as part of an aggregate
function.".

Can this work? Can I get MIN and MAX values from a column even though
there're strings in the the column?

Thanks.


Jesper

 

[Jesper F]

You need to run the Max() and Min() on an expression that returns the

number (if the value is numeric) or Null (if it isn't). E.g.:

SELECT
Max(IIF(IsNumeric(Nz([F1], "")), CDbl([F1]), Null)) As Biggest,
Min(IIF(IsNumeric(Nz([F1], "")), CDbl([F1]), Null)) As Smallest
FROM TheTable;

Awesome! Thanks very much!

Jesper