Auto Number + Current Year

[salad]

Hi, I am creating a database for my office. Our current invoice numbering system is like this: INV23-123 The first INV is set for every record, the 23 is a short form for 2003, and the 123 is the invoice number. How can I put the current year (I can put INV2003-123 if that's the only way to do it) and the auto number generated together? I tried to put in the format as "INV"yyyy"-"0000 but Access changes it to "INV"yyyy"-0000" automatically, and there my auto number is lost. Can someone please please please help me out. Thanks a lot!

 

[Jeff Boyce]

A review of this newsgroup (.tablesdbdesign) will reveal a strong consensus
AGAINST doing what you described.

First, Access Autonumbers are designed to be (essentially) unique row
identifiers, nothing more. They are not fit for, and usually are not used
for "human consumption."

Second, if your "short form for 2003" is "23", what will you use in 2013?

Next, it is considered poor relational db design to jam a number of
different fields into one.

But the good news is...

Instead of trying to do all that to the data before it goes into your table,
just store the separate data elements (look up normalization). Then, when
you need "INV2003-123", use a query to do the concatenation and formatting.
You can use that query as the source for any forms or reports in which you
need/want the combined string.

Good luck

Jeff Boyce
<Access MVP>

 

[salad]

I am pretty new in this, so, how can I set a query to generate this invoice number/primary key that I want and then have access take the result from that query and insert it to the table that I want? Or, how does this work? Thanks

 

[salad]

Hi, I have created a new table and a query that gives me the Ref No. that I want, i.e. INV2003-123 So, now, how can I take this value and put this into a table? How can I automatically create a new entry from the query and put it into the primary key field everytime I need to create a new entry in my Invoice table?

Thanks a lot.

 

[Jeff Boyce]

Please re-read my response. I am, as many of the folks who respond here,
recommending that you NOT record that composite invoice number/code.

Instead, store whatever pieces of it you will need in your table, and
"assemble" it on-the-fly, using a query, for use in forms, reports,
elsewhere.

Good luck

Jeff Boyce
<Access MVP>