MICROSOFT ACCESS – DMAX NOT WORKING PAST 99999

Weird problem, I couldn’t see why DMax +1 function wouldn’t increment past 99999, even though it had been working for 5 years.

SalesRec1 = DMax(“txtSalesReceipt”,”qrySOPaym”) + 1

Can you spot the mistake?

Well, firstly, what is the max? 99999, so the max + 1 is 100000. The problem is txtSalesReceipt is a TEXT field, so even with 100000 added to the database 99999 is still the max because 1 comes before 9 alphabetically. So I converted it to a NUMBER field and all was OK.