I want to create a recordset to select the last invoice added to a dbase.

When inserted, each invoice has the invID field generated automatically so to select the newest invoice I want to create the recordset to select the record with the highest invID.

I have tried the following but I get errors

SELECT MAX invID
FROM acc_Invoice

Does anyone know what the problem is.

I am using. Access.mdb, asp, vbscript. UD4

Thanks


Russell

Format is:

SELECT MAX(FieldName) AS SomeName FROM TableName

So with your Db:

SELECT MAX(invID) AS MaxInvID FROM acc_Invoice


Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Many Thanks Owen.

This works a treat.

Now I am going to try to put this value into a session variable for tracking purposes.

Do you think this will work?

<% Session("InvoiceID"=RS_invID.Fields.Item("MaxInvID".Value %>

Many Thanks

Davrus

Russell

Many Thanks Owen.

This works a treat.

Now I am going to try to put this value into a session variable for tracking purposes.

Do you think this will work?

<% Session("InvoiceID"=RS_invID.Fields.Item("MaxInvID".Value %>

Many Thanks

Davrus

Russell

There's nothing wrong with the code:

<% Session("InvoiceID"=RS_invID.Fields.Item("MaxInvID".Value %>

I assume you are using this to find and track the record a user has just created, something like:

<%
INSERT RECORD CODE
%>
<%
SELECT MAX RECORD CODE
%>

A word of caution, the SELECT MAX method is not guaranteed to retreive the record that a particular user has just created. If there are many users on-line at the same instance and one or more hit the submit button that triggers the INSERT - SELECT MAX code at the same time there's a chance that some of them will get the wrong record as a new record has been inserted by another user in the tiny fraction of a second between their record being inserted and the SELECT MAX code being executed. This meyhod is fine for a sngle user system.

However there is a solution, take a look at this post:
www.udzone.com/forum/topic.asp?TOPIC_ID=17360&FORUM_ID=3&CAT_ID=2&Topic_Title=Insert+Record+%28+simple+I%27m+Sure+%29&Forum_Title=Dreamweaver+UltraDev

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo