How do I convert 06-1606 to 200601606 in a query (expres. build)?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anelson - 28 Jul 2005 17:17 GMT

I am building a query that captures alot of info, to feed into another
database. However, the access db has the old ID and the new database has the
new ID. I need to convert 06-1606 to 200601606 (as an example). Any ideas?

Reply to this Message

Brian - 28 Jul 2005 17:49 GMT

If you want to get text output, set the UpdateTo for the NewID to be:

"20" & Left([OldID],2) & "0" & Right([OldID],Len([OldID])-3)

If you want numeric output:

Val("20" & Left([OldID],2) & "0" & Right([OldID],Len([OldID])-3))

This assumes you want "20" in front of each one. If you want something else,
replace it with a reference to the data that goes there instead of "20". If
the lengths differ, you may have to adjust also.

> I am building a query that captures alot of info, to feed into another
> database. However, the access db has the old ID and the new database has the
> new ID. I need to convert 06-1606 to 200601606 (as an example). Any ideas?

Reply to this Message

Tim Ferguson - 28 Jul 2005 18:13 GMT

> I need to convert 06-1606 to 200601606 (as an example)

NewString ="20" & Mid$(OldString,1,2) & Mid$(OldString,3,4)

Hope that helps

Tim F

Reply to this Message