SELECT MAX(fldOrderID) problem

Hi

This select works:
Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")

Then I can do this and it works
txtOrderID = MR_OS.grstCurrentRS![fldOrderID]

So I slightly changed that select to include a Max(field) and now I get an error

Set grstCurrentRS = gdbCurrent.OpenRecordset("Select MAX(fldOrderID) from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")

The Error says Item not found in this collection
and points to

txtOrderID = MR_OS.grstCurrentRS![fldOrderID]

Why cant I show the Max fldOrderID in that text box now.
When you use an aggregate operator like Max( it returns the Max value from that field according to all the info I have found, but I havent been able to figure out how to retrieve the results without an error.I tried to put the results into a variable also among other things and that does not work either
ex:
glngMaxOrderID = grstCurrentRS

and it said type mismatch

the fldOrderID is of type long

FYI

This works I had to use an alias to get it to work

Public Sub GetMaxOrderID()
'using a select max with an alias as max_order to get the max value from fldOrderID and store it in a variable
Dim RS As Recordset
Dim plngMaxOrderID
Set RS = gdbCurrent.OpenRecordset("Select MAX(fldOrderID) AS MAX_ORDER from tblOrderMaster " & _
"Where fldCustomerID Like " & " '*" & glngCustomerID & "*'")
plngMaxOrderID = RS![MAX_ORDER]
glngNewOrderID = plngMaxOrderID + 1
RS.Close
End Sub


txtOrderID = glngNewOrderID

[Edited by VB_Sponge on 04-24-2000 at 07:32 AM]

[Jaguar]

If you want to know the reason why this happens!? It's because the query engine is not going to return the standard field name as it does in the first query because you are using and aggregate function in connection with a field. Once you "wrap" an aggregate function around a field, it will now return something like this. "MaxOfFldOrderID".

And yes, the Alias suggestion takes care of the issue because you are overriding the change that the query engine performs on it to return back a unique field name that won't conflict with another field name.