Create Calculate Field to establish Alternate Unique Identifier

[DDPMF]

In support of a data conversion effort, I'm trying to create a calculated field within a query to create a unique id from an access table for every record within the database. I was hoping to do this without having to create a sub-routine.

When the database was initially created, the index (SSN) that was initially established cannot be used or converted due to legal issues, but I need to establish a unique identifier to identify each record within a data conversion process. This new calculated field must be a text field and have a size of 15 bytes.

If anyone can help, please drop me a line.

 

[gwinn7]

If you were using another database system, like PostgreSQL, I might suggest using a SEQUENCE, but in Access, I don't see any other way but to write a static function to create your dynamic unique ID.

Here is a simple prototype skeleton for what I am suggesting...

public static function createmynewid(myvalue) as string
static myvar as string
' Get my ID and increment myvar
end function

To use this concept, you MUST pass a value in order for this to work, because of how the JET database engine optimizes the query. Rather than repeating the function call, it will call it once, cache the result, and re-use the single result for all records. To avoid this problem, you will need to pass it a value that varies in content to 'myvalue'. Then voila, your unique id will be generated in your column.

Hope that helps,
Gary
gwinn7

 

[PHV]

Why not adding an AutoNumber field in your table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886