Hi everyone,

I have problem displaying my data in an asp page. The problem lie in the sql statment when i need to use the date as one of my condition for retrieving the data.. I haf send the code in the attachment for easy viewing.. But i will highlight the code that is causing the problem here...


The code is


<script language="javascript">
function openwindow(theURL, WinName, features)
{
window.open(theURL, WinName, features);
}
</script>

<!-------------------------------------------------------------------------- FOR EDIT - START -->
<% function aa_edit() %>
.
.
.


On Error Resume Next

if (request.form("startdate") = "") then
xdate = now()
tempsdate= dateAdd ("D",-7,xDate)
sday = day(tempsdate)
syear = year(tempsdate)
smonth = MonthName(month(tempsdate))

tempedate= now()
eday = day(tempedate)
emonth = MonthName(month(tempedate))
eyear = year(tempedate)

startdate = sday&"-"&smonth&"-"&syear
enddate = eday&"-"&emonth&"-"&eyear

else

tempsdate= request.form("Startdate")
sday = day(tempsdate)
syear = year(tempsdate)
smonth = MonthName(month(tempsdate))
tempsmonth = month(tempsdate)

tempedate= request.form("Enddate")
eday = day(tempedate)
emonth = MonthName(month(tempedate))
tempemonth = month(tempedate)
eyear = year(tempedate)

if datediff("D", tempsdate, tempedate) <= 0 then%>
<script language="javascript">
window.open("../inc/errordate.asp", "Error", "width=350,height=140");
</script>
<%end if

startdate = sday&"-"&smonth&"-"&syear
enddate = eday&"-"&emonth&"-"&eyear
end if

On Error Goto 0

set rs2 = server.createobject("ADODB.recordset")

sql2 = " SELECT DISTINCT LotNo, quantity, Water, HOCA, TotalNitrogen , Oil , ASH, HOSU, Iron, Assay, Color , Fines, ParticleSize , Approved, date_authorized, Allotted, emft, special_lot from aalabresult where approved=1 and Coa_Selected = 0 and date_authorized between '"&startdate&"' and '"&enddate&"' ORDER BY LotNo "


%>

.
.
.
<% end function %>
<!-- END -->



Hope to hear from your answer soon,
Thx alot

sql2 = "SELECT DISTINCT LotNo, quantity, Water, HOCA, TotalNitrogen , Oil, ASH, HOSU, Iron, Assay, " & _
"Color, Fines, ParticleSize, Approved, date_authorized, Allotted, emft, special_lot " & _
"FROM aalabresult WHERE approved = 1 AND Coa_Selected = 0 AND " & _
"date_authorized BETWEEN #" & startdate & "# AND #" & enddate & "# ORDER BY LotNo"