Ignore Padding in Update Query

[Guest]

When checking field data for a match in an update query, I need to ignore the
padding (0s).
example: T1 Field1(Primar Key) T2 Field1 (Primary Key)
362957 00362957
83097 00083097
1989836 01989836

I need the system to recognize these as the same record.
Also, how do I ensure that if T2 has a record that does not appear in T1
that the update will add that record to T1. I do not want to delete records
from T1 that are not in T2.

 

[Guest]

INSERT INTO T1 ( Field1 )
SELECT Trim(CLng([Field1])) AS Expr1
FROM T2
WHERE (((Trim(CLng([Field1]))) Not In (Select Field1 from T1)));

This will only insert the Field1. You need to add the other fields. As
always, it's a very good idea to make a backup before messing with data the
first time.

Oh. It will fail if any of the data in Field1 can not be converted into a
number so make sure that there aren't any Oh's instead of Zeros or any other
alphabetical characters.

 

[Duncan Bachen]

When checking field data for a match in an update query, I need to ignore the
padding (0s).
example: T1 Field1(Primar Key) T2 Field1 (Primary Key)
362957 00362957
83097 00083097
1989836 01989836

I need the system to recognize these as the same record.
Also, how do I ensure that if T2 has a record that does not appear in T1
that the update will add that record to T1. I do not want to delete records
from T1 that are not in T2.

Since you're storing them as a string value but they are numbers, you
could coax the value into a number with Clng(Field1). So you could
compare, If Clng(T1.Field1) = Clng(T2.Field2).