Is null? median query

[lcfc]

I am using this code to obtain the median from a field:

SELECT Min([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN
(SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] ORDER BY [Time Arrive to Dr] DESC)
UNION SELECT Max([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN (SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] ORDER BY [Time Arrive to Dr] ASC);

This produces either 1 number or 2, from which i can find the average and thus get the median.

Unfortunatley there are some null cells in the field which i don't want to include in the calculation. How can i filter these out, i.e, where do i put "is not null" in the above code. Thanks

 

[wemeier]

SELECT Min([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN
(SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] WHERE [Time Arrive to Dr] Is Not Null ORDER BY [Time Arrive to Dr] DESC)
UNION SELECT Max([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN (SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] WHERE [Time Arrive to Dr] Is Not Null ORDER BY [Time Arrive to Dr] ASC);


Cruising the Information Superhighway
_{(your mileage may vary)}