MS Access

[Guest]

I have an database whete one of the fields is the main identifier -
autonumber format with a preset layout - (AA/#/2007). I need to copy the
actual value of this field into a second field within the same table. Thus,
the new field need to display the complete value - (AA/001/2007) in text
format. - how do I setup this " text" field to display the value of the
"autonumber" field.

The reason for this is that I use this dataset in SAS coding, and on using
import, it only display "1" and not "AA/001/2007", resulting in other
problems with the reposrts generated.

Tx

 

[kingston via AccessMonster.com]

Could you use a query with a calculated field instead for your import:

"AA/" & Right("000" & [AutoID],3) & "/2007"

 

[John W. Vinson]

I have an database whete one of the fields is the main identifier -
autonumber format with a preset layout - (AA/#/2007). I need to copy the
actual value of this field into a second field within the same table. Thus,
the new field need to display the complete value - (AA/001/2007) in text
format. - how do I setup this " text" field to display the value of the
"autonumber" field.

The reason for this is that I use this dataset in SAS coding, and on using
import, it only display "1" and not "AA/001/2007", resulting in other
problems with the reposrts generated.

Tx

The autonumber field actually contains just a long integer number. The format
isn't stored.

You do NOT need to or want to store the text field separately. If you need to
export the data to SAS, do so from a Query; you can include a formatted field
in the query by typing

Format([fieldname], "AA\/#") & Year(Date())

assuming that the 2007 is in fact the current year.

Note that Autonumbers are a VERY BAD CHOICE for this purpose - they are not
editable, will inevitably get gaps, and range up to over two billion (and can
even become negative). You may want to consider using an Integer or Long
Integer field and maintaining it yourself, manually or with code.

John W. Vinson [MVP]