Tarting up an autonumber

kupe

Registered User.
Local time
Today, 14:31
Joined
Jan 16, 2003
Messages
462
Will autonumber take a prefix?

It would be handy to have the autonumbers in each of several tables carry two or three letters in front of the number so that when combined in a joint table, the user sees instantly which table the record belongs to.

(Phew. It ain't 'alf 'ot in Warwickshire.)
 
Autonumber with letters

The autonumber is a number. However you could use a computed id consisting of the autonumber and letters, i.e.,
StrId = "TTT" & intID to accomplish the same thing.
 
Yes, that looks a real possibility, FuzzyGeek, thanks very much.

You'd think it would be easy to do in a table. Like the ID for tblFuzzyGeek could be FG+autonumber, as in - for example - FG33321. But Access seems reluctant.

Will report back. Cheers.
 
In Table Design
Field: Your Field
Format: "YourPrefix"#


This is cosmetic only, the prefix is added at runtime for presentation purposes and is not stored because it adds nothing to the key.
 
FuzzyGeek's so far hasn't worked for me. But, Fornatian, yours does, really well. And it is a handy tip to know. Thanks very much.

But, Ian, for some reason it doesn't want to pass through a Union Query. There doesn't seem a logical reason. It works perfectly happily through a select query.
Any ideas?

Meanwhile, thank you very much, gentlemen.
 
Hi Ian

That's such a good tip, but no matter what I rebuild, I can't get a union query to show the prefix. Have you any experience of this sort of thing? Cheers
 
take note of what Fornatian notes:

This is cosmetic only, the prefix is added at runtime for presentation purposes and is not stored because it adds nothing to the key.

Since the # is picking up the record no on the form, when building a query, no number is returned before the query is run

HTH
Dave
 
Use a calculated control in your original query to build the prefix and autonumber:

In the column header type:

MyPrefixedPK: "YourPrefix"&[YourAutonumberField]

Do this for each table and then run the Union query, should work.
 
Thanks, OldSoftBoss, and yes I do note Ian's advice. But the odd thing is it gets through the normal queries. But not a union query which doesn't make sense. Well, it didn't till you explained further. Yet there it is getting through select queries happily enough yet stumbling at the Union Query. Cheers
 
Yes, many thanks, Ian, that works well. Each table now has its query to handle the prefix + ID. It is handy being able to use it at the table design level because it works fine in normal queries. Very pleased to have that knowledge, thank you.
 

Users who are viewing this thread

Back
Top Bottom