User formatted autonumber question

[Ranger7913]

I am creating a database to produce reports, invoices, track clients, etc. It will essentially be used to create an investigative report. I created a "File Number" field set as an autonumber so each file would have a new file number. In the properties of this field, I changed the format to indicate "1823-0"000. This would allow each file number generated to have "1823-0" along with the auto generated number now being 3 digits. The problem I have is in establishing relationships in related tables. The data in the other tables recognizes the field as text so it wont allow me to link it due to incompatible fields. Any suggestions? Also, will this affect any other aspects of my database (running queries). My goal is to link multiple tables (clients, customers, details, exhibits, etc) in order to produce a report. I enjoy this site as it has helped me on many occasions. Thanks

[JamesDeckert]

Why do you want 1823-? in front of the number?
You could append this in any form which displays the number so the number in the table is not changed.
There may be a better way, but this is certainly an option.

[Ranger7913]

James. Thanks for your response. 1823 is related to my business. I want in included in the file number. The actual preference is to have it generate something like 1823-month(02)-4 digit file number. If you have any ideas how to accomplish that it would be greatly appreciated. I will do some research on appending info. I am still learning a lot of the aspects of access. Thanks again

[ssanfu]

As James said, that should be "calculated" in a query.

I created a "File Number" field set as an autonumber so each file would have a new file number. In the properties of this field, I changed the format to indicate "1823-0"000.

You are using the autonumber field wrong. You should read these sites:

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm