Aggregate function MAX ignores NULLs

I need to run a query that will pull the most recent revision, and it
must be based on the RevisionDate (if you need to know why it is
because the revision column can contain letters, numbers, hyphens, or
underscores - numbers, in this case have the highest value, but SQL
interprets letters as having higher values), and give me the result set
below. The problem is that using the aggregate function MAX ignores
the null values, so the revision information for drawingID 37000 is
left out of the result set in the current query, which is below.
Sample Data (sorry I couldn't line up these columns better, the google
text editor keeps moving them):
tblDrawings
ID DrawingNo
37000 R2-S01
36455 431001200201
tblDrawingRevisions
ID DrawingID Revision RevisionDate Status
281213 36455 _ NULL ApprovedAs Noted
281781 36455 1 2006-07-27 Approved
281703 37000 -- NULL Approved for Constr.
Current Result Set:
DrawingID Drawing No Revision Status
37000 R2-S01
36455 431001200201 1 Approved
Result Set Needed:
DrawingID Drawing No Revision Status
37000 R2-S01 -- Approved for Constr.
36455 431001200201 1 Approved
Current Query:
SELECT dr.ID as dwgID,
dr.DwgNo as dwgNo,
dRev.DrawingRev,
dRev.RevStatus as dwgRevStatus
FROM tblDrawings dr
LEFT JOIN (SELECT t1.ID, t1.DrawingID, t1.DrawingRev, t1.DwgRevDate,
t1.RevStatus
FROM (SELECT ID, DrawingID, DrawingRev, DwgRevDate, RevStatus
FROM tblDrawingRevisions)t1
INNER JOIN
(SELECT DrawingID, MAX(DwgRevDate) as MaxDate
FROM tblDrawingRevisions GROUP BY DrawingID)t2
ON t1.DrawingID = t2.DrawingID AND t1.DwgRevDate = t2.MaxDate)dRev
ON dRev.DrawingID = dr.ID
WHERE dr.dwgno = 'r2-s01' or dr.dwgno = '431001200201'
Please help!Keep in mind NULL means "Unknown". So you can't say Unknown is equal
to anything. You can't even say Unknown is equal to Unknown. So if it
is NULL I would default the DwgRevDate to the create date (you must
have one?). Otherwise just use a very early date for the default
isnull(t1.DwgRevDate,'01/01/1900').
So your SQL becomes:
SELECT dr.ID as dwgID,
dr.DwgNo as dwgNo,
dRev.DrawingRev,
dRev.RevStatus as dwgRevStatus
FROM tblDrawings dr
LEFT JOIN (SELECT t1.ID,
t1.DrawingID,
t1.DrawingRev,
t1.DwgRevDate,
t1.RevStatus
FROM tblDrawingRevisions t1
INNER JOIN
(SELECT DrawingID,
isnull(MAX(DwgRevDate),'01/01/1900') as MaxDate
FROM tblDrawingRevisions
GROUP BY DrawingID)t2
ON t1.DrawingID = t2.DrawingID
AND isnull(t1.DwgRevDate,'01/01/1900') = t2.MaxDate)dRev
ON dRev.DrawingID = dr.ID
WHERE dr.dwgno = 'r2-s01' or dr.dwgno = '431001200201'
JJ