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

G

Guest

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Query problem 1
looping thru records in a table 1
Build Dynamic Query from Form 6
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
Access Overflow error 3
How do I link a lookup query to an ADO table? 2
how do I query the current Win logon id in a nonsecure Access db 4
How do I convert dates (09/15/06 or 09/20/06) to month (Sept 06)? 3

Top