SQL Search

[Chopstik]

Am trying to find the the MAX field that exists in a database where the first five characters fit whatever the user entry may be (in other words, it needs to be dynamic. I keep coming back with a syntax error near PurchaseOrderID. Here's my code, can anyone tell me what is happening? Thanks.

strTest = "SELECT MAX PurchaseOrderID FROM PurchaseOrder WHERE PurchaseOrderID LIKE '" & _
left(strPONum,5) & "'"

 

[rico14d]

try

max(PurchaseOrderID)

 

[ros]

You need parens around the field name. Also, don't you need the wildcard % ?
strTest = "SELECT MAX(PurchaseOrderID) FROM PurchaseOrder WHERE PurchaseOrderID LIKE '" & _
left(strPONum,5) & "%'"

HTH,
Richie

 

[Chopstik]

Thanks, ros and rico, that helps a lot. Much appreciate it!