By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

how can I autogenerate value for a text field

P: 23

I have a customer table with a Primary field name "CustomerCode" .

I want that the value in the field should generate automatically, like A001 To A200 and then B001 to B200 and go on

Is it possible?

Jan 4 '14 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,287
Moonrb: Asking for code is against the site rules; however, if you will search back in the access forum, several versions of this have been covered and in many different methods.

In your case, I would have two fields, on with the A, B, C, etc.. and a second with the sequence (1,2,3,... 198,199,200)
The query would be to group (and sort) on letter field and return the max of the sequence field now would would get a table of:

Now I would do a findfirst to return the record where [sequence]<200, if no records, then move to the last record to find the next letter, I use the ascii values and just return the max, converting to letter in the calculated formula for the field/control

there are 20 other different methods... (^_^)
Jan 4 '14 #2

Expert Mod 15k+
P: 31,261
Using a DMax() call on the existing data will give you the last one used to date. From there is should be very straightforward to work out the next in the sequence and use it.

NB. This sequence gives you a maximum number of 5,200 records. Few real-life situations will manage with that for very long. Possibly yours might, but I'd advise you to be very certain at the design stage as it's so much harder to fix later on.
Jan 5 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.