How to include this SQL subquery for absolute number's value?

Question

I have this query written for Access DB.

queryText = "SELECT Technicians.ID, firstName, lastName, Technicians.[Zone],
COUNT(technicianAssignedId) AS JobsDone, 
MAX(Faults.timeCompleted) AS LastJobTime,
MIN(Technicians.[Zone] - & faultZone &) AS Distance ' MIN has no use
FROM Technicians LEFT OUTER JOIN Faults ON
Technicians.ID = Faults.technicianAssignedID 
WHERE Specialization = '" & specialization & "'
`AND Availability = 'available' 
GROUP BY Technicians.ID, firstName, lastName, Availability,` Technicians.[Zone], Specialization 
ORDER BY 7 DESC"

I have this to get absolute value

SELECT CASE
WHEN value < 0 THEN value * -1
ELSE number *1 END
AS VALUE
FROM DB;

Now the column with Distance alias may end up as negative number which I need as positive by using (* -1), but I can't figure out how to put this together.

Thanks for any help

Why not just use the ABS(value) Function? – JamieSee Oct 09 '12 at 17:27 — JamieSee, Oct 09 '12 at 17:27

score 5 · Accepted Answer · answered Oct 09 '12 at 17:26

5

try

ABS(Technicians.[Zone] - & faultZone &) AS Distance

See ABS doc

answered Oct 09 '12 at 17:26

juergen d

201,996
37
293
362

crap, I tried that before posting, it didn't work, I thought ABS() doesn't work with accces but it is working now... – JanT Oct 09 '12 at 17:30

score 3 · Answer 2 · answered Oct 09 '12 at 17:27

3

Why can't you just use the absolute function, ABS?

answered Oct 09 '12 at 17:27

LittleBobbyTables - Au Revoir

32,008
25
109
114

score 1 · Answer 3 · answered Jan 07 '13 at 19:37

1

ABS() is working for all SQL versions

answered Jan 07 '13 at 19:37

BSergei

21
3

How to include this SQL subquery for absolute number's value?

3 Answers3

Linked