[AccessD] Dire ned of Help

A thousand thanks, Stuart!!!

I had started to look in this direction, but told myself that, "no, it
is working for everything else, so that must not be it." I'm a dolt! I
knew it would be simple and I'd be hitting myself saying, "Duh!"

Thanks again!

John W. Clark
Computer Programmer
Niagara County


>>> "Stuart McLachlan" <stuart at lexacorp.com.pg> 10/6/2006 8:36 PM >>>
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


-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com