sql select group by int()

I want to get int using this query:

    "SELECT int(Abs(x1-x2)+0.5) AS len, Count(*) AS num FROM Coordinates
         GROUP BY int(Abs(x1-x2)+0.5) 
         ORDER BY int(Abs(x1-x2)+0.5) ASC;";
int len = reader.GetInt32(reader.GetOrdinal("len"));

But I get double(x1 and x2 type). I’m using Access db. Group by and order by len doesn’t work.

Answer

Both int and floor functions don’t change types but just do rounding. You need CLng or CInt functions.

Each function coerces an expression to a specific data type.

Syntax

CBool(expression)

CByte(expression)

CCur(expression)

CDate(expression)

CDbl(expression)

CDec(expression)

CInt(expression)

CLng(expression)

CLngLng(expression) (Valid on 64-bit platforms only.)

CLngPtr(expression)

CSng(expression)

CStr(expression)

CVar(expression)

The required expression argument is any string expression or numeric expression.

Return Types

The function name determines the return type