SQL + ASP...

[CtrlAltDel]

can someone tell me whats wrong with this SQL statement that works fine in Access, but wont work on my ASP page.

Select *
From Status
Where (Status.Quote_No)=9600003 and (Status.Date) = (Select max(Status.Date) from Status)

im confused as to why that wont work on my ASP page

i get the error on the line that is trying to open the RecordSet with that SQL statement.

[Mark Sreeves]

What error are you getting?

[TheLeeMan]

Yes, this is a rather simple mistake to correct for. When you refer to the field names ("quote_no", "date", etc) you do not need to respecify the table name since that is declaired in the first part of the query.

Also, you can not have a query inside of other another query as you did in the second part of your statement (I'm not sure how this works in other SQL methods, I'm purely ASP).

Your statement would look as so:

<%

' CODE TO OPEN DB FILE

SQL = "Select max(Date) as MaxDate from Status"
Set rs = objConn.execute(SQL)
     myDate = rs("MaxDate")
SQL = "Select * From Status Where Quote_No=9600003 and Date=" & myDate
Set rs = objConn.execute(SQL)

' CODE THAT USES DB INFO

' CODE THAT CLOSES DB FILE

%>

I must also warn you about using 'date' for the name of a field. The Access DB drivers seem to run into problems with several field names ('date' being one of them) when you try to create a new entry or update a curent entry. Pulling info out of the DB however, has never caused me a problem.

Hope this helps!

--
TheLeeMan