[RESOLVED] syntax error "select max(fields) from table"

I have a table "ORDER" database access 2k its structure is:

orderid = autonumber
orderdate= date
supplierid=text
suppliername=text

in my form have a textbox name "txtnota" and a command button click event code:

VB Code:

Private Sub command1_Click()
    call RsTmp
end sub
 
 
Sub RsTmp()
Dim RsTmp As ADODB.Recordset
 
msql = "select * from order " & _
       "where orderid in (select max(orderid) from order)"
[b]Set RsTmp = con_cashReg.Execute(msql) 'error here[/b]
If Not (RsTmp Is Nothing) Then
   If (Not RsTmp.BOF) And (Not RsTmp.EOF) Then
      txtnota.Text = RsTmp.Fields("orderid").Value + 1
   Else
   txtnota.Text = 1
   End If
   RsTmp.Close
End If
End Sub

the run time error is:
"syntax error in FROM clause"
Im regard it can get max number from "OrderId" field and add 1 to it
i dont know what wrong with this, If any one can help me is very much appreciated. thanks in advance

best regard

Additional information;

when I change table to this structure then working fine: (Orderdetail)

OrderId=number
ProductId=text
ProductName=text
Unit=text

I just simply change the Order to Orderdetail

If any one can help thanks

best regards,

The problem is that Order is a reserved word (from "Order By"), so should not be used as a table or field name, as it leads to the database engine getting confused.

What you should do is re-name the table. An alternative is to put square brackets around the name (eg: "from [order]"), but this will not work in all situations.

Hi Si the geek

thanks, Your quick replay is appreciated
good rate

One more turn,

hi ,.si the geek, can u tell me "[]" in what situation doesnt work?
thanks

I don't know about situations where it wouldn't work, only situations were it is required (like using reserved words).

To be completely honest, I cant remember! :D

All I know is that there have been cases on the forums where it doesn't work.