Problem with MAX function

[gdomino]

I am trying to return a Max value from a table to a form. I keep getting the error message "Too few parameters. Expected 1"

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = DBEngine.OpenDatabase("C:\BarCode.mdb")

Set rst = db.OpenRecordset("SELECT Max(Clnid)AS MaxOfClnid FROM Clones WHERE Clnplateid = txtCln_plate_id;")


This function has always worked and I don't know what is going wrong this time.

 

[bjackson]

is txtcln_plate_id a form control name

if so try

Set rst = db.OpenRecordset("SELECT Max(Clnid)AS MaxOfClnid FROM Clones WHERE Clnplateid = " & txtCln_plate_id & ";")

 

[gdomino]

Thanks bjackson.

Yes txtcln_plate_id is the form field.

Still have a slight problem. Now I am receiving a syntax error "missing an operator in the query expression". The message box is showing

'Clnplateid = 1234567890' ..........which is Clnplateid = " & txtCln_plate_id & " in the expression.

Again thank you for the help. I am really impressed by the assistance the members of this forum offer.

 

[geralf]

gdomino,

Seems like there might be a space or two missing in the sql. Is there a space after the paranthese )AS (should be ) AS, and after the = sign?

hth
Gerhard

 

[gdomino]

I got it to work. I just needed to put 3 sets of quotes around the form control. Thanks again!