Data Mismatch with join

[Jim]

I have a table where the field is a TEXT field 20 characters long that I need
to join to another table with a field that is a NUMBER field 50 characters
long. The data (and 8 digit integer) is the same. How do I convert TEXT to
NUMBER or vice versa?

 

[Marshall Barton]

I have a table where the field is a TEXT field 20 characters long that I need
to join to another table with a field that is a NUMBER field 50 characters
long. The data (and 8 digit integer) is the same. How do I convert TEXT to
NUMBER or vice versa?

Confusing, a number type field can not be 50 digits long.

Check VBA Help for conversion functions such as CLng, CStr
and Val.

If you work exclusively in SQL view, you can use an ON
clause like:
ON CLng(textfield) = numberfield
or
On textfield = CStr(numberfield)

It's probably not that simple, but I would need a bunch more
details to be more specific.

 

[John W. Vinson]

I have a table where the field is a TEXT field 20 characters long that I need
to join to another table with a field that is a NUMBER field 50 characters
long. The data (and 8 digit integer) is the same. How do I convert TEXT to
NUMBER or vice versa?

HUH?

Numbers cannot be 50 characters long. In fact they're not stored as characters
at all; they're stored in binary, and the displayed size of the field is
generated at runtime, not stored in the field.

What is the actual datatype (e.g. Number... Long Integer, or Number...
Decimal, with the scale and precision) of the NUMBER field?

You can get a (non-indexed, inefficient) join by using CStr() on the number
field, or the appropriate conversion function (CLng, CInt, CDec, CDbl) on the
text field.

Perhaps a couple of examples of the content of the field would help (leading
zeroes will make a difference for example).