Want to select last entry, having query probs

[Valdura]

Hi,

I am somewhat new to database queries. First, my problem is I have a database, I am confused as to whether it is a JET Database or an Access database. When I open the .mdb file it opesn as Access 2000 file format but when I execute a page with a query on the server I get an error stating a MS JET Database Engine error has occurred. Maybe somebody could fill me in if these are one in the same or not.

Anyways, my main problem is I have an ASP page I have taken over from a developer who is on vacation and the query they have now pulls in the first record and we need the last record to be pulled in.

This is the query that is there now, which works but always pulls in the first record, not the last:

rsDetails.Source = "SELECT TOP 1 * FROM tblJustPartners"

I have done some research on the web and from what I can tell to pull the last record I would use:

rsDetails.Source = "SELECT MAX(ID) * FROM tblJustPartners"

But whne I replace the original query with my query I get the following error:

Microsoft JET Database Engine error '80040e14'

Cannot have aggregate function in WHERE clause (ID=MAX(ID)).

/surveys/thankyouJUST.asp, line 17

I have also tried:

nTop = "SELECT MAX(ID) FROM tblJustPartners"
rsDetails.Source = "SELECT * FROM tblJustPartners WHERE ID = " + nTop + ""

But, I get the following syntax error:

Microsoft JET Database Engine error '80040e14'

Syntax error. in query expression 'ID = SELECT MAX(ID) FROM tblJustPartners'.

/surveys/thankyouJUST.asp, line 19

Note, if I manually choose the ID then it pulls up an entry using:

rsDetails.Source = "SELECT * FROM tblJustPartners WHERE ID = 11"

Can anyone give some clues here as to what is wrong with the construction of my query.

Thanks,

-Steve

 

[IanH]

The TOP 1 will choose the first record in a recordset. So you need to think about how you sort the order of the records. You say there is an [ID] field, and you would like the record with the maximum value in this field. Therefore logically you want the order sorted by ID field in a descending order.

Try ...

rsDetails.Source = "SELECT TOP 1 * FROM tblJustPartners ORDER BY [ID] DESC"


Regards

Ian

 

[Valdura]

Excellent!! This did the trick! Thanks Ian.

 

[IanH]

No problem! I like to keep it simple if I can