Stuart McLachlan
stuart at lexacorp.com.pg
Fri Oct 6 19:36:01 CDT 2006
On 6 Oct 2006 at 11:32, John Clark wrote:
> strSQL = "SELECT nz(Max(Mid([tblMain]![txtControlNum],4)),0)+1 AS
Mid([tblMain]![txtControlNum],4) will return "999" or "1000", as text
strings. "999" is greater than "1000" when considered as a string - the
first starts with ASC("9") the second with ASC("1")
You need to return the maximum numeric value of the the sequence number,
not the greatest string value.
Use: nz(Max(Val(Mid([tblMain]![txtControlNum],4))),0)+1
--
Stuart