Create Relation on linked tables with different data formats 1

[InterGeorge]

I have two linked tables (ODBC, read only) where I need to link tables (relation)
Now the invoice is Transaction_ID = 7 == invoice
There the field is Number_ID(text) and in the invoice the invoice_ID is numeric. how can I link this text to numeric??

I have one empty field (Link_ID(numeric)) available.

How do I copy the text filed (contains only numbers) to the number field. I tried "Link_ID Val(Number_ID)" but ending up with errors.

Is there a way to extract only the numbers and move them to the numeric field? Like in Pascal: ID=Num(textstring)??

I also could INSERT this table in a local one, but I have the same problem, convert the text in to numbers!

Thanks a lot for any thoughts

George

 

[mpastore]

Assuming you output filed is datatype LONG..

update mytable set Link_ID = clng(Number_ID)



Mike Pastore

Hats off to (Roy) Harper

 

[InterGeorge]

**Y E S ***

it works

two days headache come to an end!!

mhhmmm.. thiny little question:
if the field contains ASCII (I did run on all, incl. ASCII), how can I surepress error messages? Just for future use - who is perfect

 

[mpastore]

Not sure about that one, maybe docmd.setwarnings false

Mike Pastore

Hats off to (Roy) Harper