Combining Date Field with an Autonumber Field.

L

Leslie

I need to combine a date field (formated to four-digit year) with an
autonumber field in an Access 2000 Database. Is this possible? What is the
best method and expression to accomplish this concatenation?
 
D

Douglas J. Steele

At the risk of seeming flippant, why?

Combining two pieces of information into a single field is not a recommended
practise (it's actually a violation of database normalization principles).

Store the date and the autonumber as two separate fields. If you really need
to display them together, you can write a query that concatenates the two
fields, and use the query wherever you would otherwise have used the table.
 
L

Leslie

The date and the autonumber are stored as two separate fields. I need to
combine them for a work order numbering system. My attempts to concatenate
these fields, formated as I need them, were unsuccessful. The query result
converted the needed format back to the format in the table. So to rephrase,
specifically, I need to concatenate a date field with an autonumber field to
produce a display that reads: Current FY +Julian Date+Autonumber. For
example, the result would be "2004245020" to represent the 20th record
entered in the table today. However, the result would be "2005246021, to
represent the next record, entered tomorrow on 1 October, which is the start
of our 2005 Fiscal Year. This is proving to be quite a challange... any help
would be appreciated. Thanks.
 
J

John Vinson

Current FY +Julian Date+Autonumber. For
example, the result would be "2004245020" to represent the 20th record
entered in the table today. However, the result would be "2005246021, to
represent the next record, entered tomorrow on 1 October, which is the start
of our 2005 Fiscal Year. This is proving to be quite a challange... any help
would be appreciated. Thanks.
Click to expand...

How about:

Year(DateAdd("m", 3, [Date])) & Format([Date], "yyy") &
Format([Autonumber], "000")


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Leslie

Thanks John Vinson, it worked with one minor modification. Your suggestion
was just what I needed to achieve the query result. You deserve all the
kudos! Again, much appreciated.

John Vinson said:
Current FY +Julian Date+Autonumber. For
example, the result would be "2004245020" to represent the 20th record
entered in the table today. However, the result would be "2005246021, to
represent the next record, entered tomorrow on 1 October, which is the start
of our 2005 Fiscal Year. This is proving to be quite a challange... any help
would be appreciated. Thanks.
Click to expand...

How about:

Year(DateAdd("m", 3, [Date])) & Format([Date], "yyy") &
Format([Autonumber], "000")


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Top