Code

[techiesupport]

Hi,

Can anyone help?

I wish to write an AutoNumber, that is four (4) figures long, but so that each number record has a letter prefix.

E.g:

- A0123
- A0124
- A0125
- and so on...

I wish for the prefix to be the same each time, and wish to have this written into the code/formula/expression, so that when adding a new record, the record ID is completed for me.

All help would be appreciated, as I have exhausted my knowledge...

 

[Rich]

What will happen when you get to 9999 ?

 

[techiesupport]

Re:

I will have left the company beofre that happens...hehe...

no, seriously, OK, it can be six figures, or eight, the length of the figures is not really that important, is it? as long as I can get the code, it can be changed at a later point.

 

[Alexandre]

Have a look to these discussions:

Customized autonumber

Custom PK

 

[techiesupport]

Hi Alexandre,

these previous posts were great, but unfortunately, I still am unable to make sense of these posts. I have tried the "A" & "[ID]" as I believe this was the result of the posts, unfortunatley, when trying to add records to the datasheet, no information is completed in this field, so after completeing all other inofrmation, and trying to move onto the next field, I recieve an error message about a "Null Value", and how it cannot be accepted.

Can anyone point-out where I am going wrong, or another way in which to achive this AutoNumber?

("A", the letter in question, and [ID:] the AutoNumber field.)

As ever, all help is greatfully appreciated.

 

[Len Boorman]

Something I have done in the past that may be worth considering. It's a bit longwinded but you never know.
Need about 3 fields to do it
and a table
Decide on final length of number bit lets say 6
Table has integer and text field
integer represents length of a filler, text is filler
0 null
1 0
2 00
etc

Field 3 is standard autonumber
Field 2 is result of update query that finds length of Field 3
so 1=1
12=2 etc

Field 1 is the result from a query that

Field 1 = "A" & (6-field2).(Table filler) & Autonumber

To explain a bit more
the final field ends up as a concatonation of the Prefix letter plus the number of 0's needed to make up the length of the autonumber to a specified length plus the autonumber itself.

From memory I cannot be certain whether or not you will need and extra text field to rewrite the autonumber into as text so that the concatonation works. Have a feeling you may have to.

Any queries get back to me

Len B

 

[techiesupport]

Re:

Cheers Len, I thinks thats what I did to get it working, but then again...


Cheers anyway.

(I think I have found the ultimate solution to the problem: Delegate the work to the SQL Consultant, who is being paid for by another department, for two weeks, to do my work!!!, who said managers were blind/deaf/dumb???)