Select MAX ( ) +1

[marco]

Hi everyone,
I hope I post in the right place!
I have a problem with a custom autonumber field.


For most tables of my db I use 'select max(table) +1' and everything is fine. But I have some tables in which my Pk are something like 'ART00031'.
In this case my query gives me an error.
What should I change?

Thanks in advance,

[ConneXionLost]

It seems you've discovered why Access uses "Autonumber" as a table key, and not "Autoletter".

Max() is a mathematical function and is meant to apply to numbers, not letters or text strings.

If you're going to change anything (and you want to continue using Max()+1 for a key generator), you should change your keys to numbers.

Cheers,

[TheShabz]

If you insist on keeping the text, one way around it would be to make your primary key a concatenated, calculated field. I take it the ART is a category. Create a separate column for category, and one for the number. the number field would be your usual Max()+1. then your true primary key field would be pk:Category&Number