Select sum case group?

I want to retrieve all the 'unitmodule' where 'datesent' and 'datedelivered' value are not equal to Null from 'logistics' table group by 'unitmodule'.

logistics tables
ID unitmodule datesent datedelivered
1 PSU June 1, 2003 June 31, 2003
2 AFE May 12, 2002
3 AFE March 5, 2002 April 3, 2002
4 CSU
5 TRX May 4, 2004

output
unitmodule Module sent Module delivered
PSU 1 1 AFE 2 1 TRX 1 0 CSU 0 0

this is what i did:
<%
accessdb="bss"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")

sql = "select
unitmodule,
sum(case when datesent Is Null then 0 else 1 end) as datesentcount,
sum(case when datedelivered Is Null then 0 else 1 end) as datedeliveredcount,
from
logistics
group by unitmodule;"

rs.Open sql, cn
If rs.eof then
search="nothing"
end if
%>

the error message was:
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/test/defectivecount2xxx.asp, line 72

sql = "select
-------------^

I'm sure I'm writing it the wrong way. thanks.

I want to retrieve all the 'unitmodule' where 'datesent' and 'datedelivered' value are not equal to Null from 'logistics' table group by 'unitmodule'.

logistics tables
ID unitmodule datesent datedelivered
1 PSU June 1, 2003 June 31, 2003
2 AFE May 12, 2002
3 AFE March 5, 2002 April 3, 2002
4 CSU
5 TRX May 4, 2004

output
unitmodule Module sent Module delivered
PSU 1 1
AFE 2 1
TRX 1 0
CSU 0 0

this is what i did:
<%
accessdb="bss"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")

sql = "select
unitmodule,
sum(case when datesent Is Null then 0 else 1 end) as datesentcount,
sum(case when datedelivered Is Null then 0 else 1 end) as datedeliveredcount,
from
logistics
group by unitmodule;"

rs.Open sql, cn
If rs.eof then
search="nothing"
end if
%>

the error message was:
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/test/defectivecount2xxx.asp, line 72

sql = "select
-------------^

I'm sure I'm writing it the wrong way. thanks.

think of the query in english, you said it perfecly, you want all the records WHERE 'datesent' and 'datedelivered' value are not equal to Null from 'logistics'. So put that into your where clause, and check to make sure those two columns are not NULL. =P

unterminated string constant, you have to do your query like this



you have to close the string and then continue the string to the next line with & _, hope this helps, let me know if you still can't get it..