Create a record number in sequence based on criteria

[gonzo1979]

I am building a database that requires a unique number assigned to it based on criteria. Example: One field is a drop down that says AIR/GND/OD, another is a YES/NO titled Recordable. If a person checks Recordable and AIR and it's the first entry of the type, the number would be "A001". If Recordable is not checked, then the number is left blank. How do I do that?

[zmbd]

gonzo1979:
Make your life simple and have one field for the [air/gnd/od]
Have a second field for the numeric portion
a calculated field in the query/form/report can combine the two fields with formating.
Once this is done, then build query/recordset that groups on the [air/gnd/od] field and returns the DMAX() of the numeric field.
It should be straight forward from there as to you code logic.

If you've already combinded the fields, then your code will have to filter out and parse the numeric from the string which is not straight forward however, one such examplehttp://bytes.com/topic/access/answer...-number-string you'll have to store the result, and step thru all of your records to find the next number in the series... IMHO, much easier to split the fields. This process will start to bog down as your database grows too!