Link AutoNumber to Text Field

[mattc66 via AccessMonster.com]

Hi All,

I have 2 tables that I need to link the AutoNumber to a Text Field in another
table. Is this possible?

If so, how do I do it?

Matt

 

[Guest]

No you can not as one is datatype of long integer and the other is text. The
data stored for a numerial one and a text one are totally different.

Why not change the text field to a number, long integer?

Alternately I think you can create a query of the table that has the text
field and output the field as a number field.
NumField: CLng([YourFieldName])
Then link your autonumber table to the query.

 

[mattc66 via AccessMonster.com]

The trouble is the data comes from an external UPS database that has some
items in the fld that are text. I am trying to only link the items in the
table that match to my database which is an autonumber fld.

No you can not as one is datatype of long integer and the other is text. The
data stored for a numerial one and a text one are totally different.

Why not change the text field to a number, long integer?

Alternately I think you can create a query of the table that has the text
field and output the field as a number field.
NumField: CLng([YourFieldName])
Then link your autonumber table to the query.

Hi All,

[quoted text clipped - 4 lines]

Matt

 

[Guest]

Ok, then you will need to do the query and also use another field like this ---
NumField_2: Val([YourFieldName])
Then have criteria > 0

With the criteria you will pull only nunbers.

The trouble is the data comes from an external UPS database that has some
items in the fld that are text. I am trying to only link the items in the
table that match to my database which is an autonumber fld.

No you can not as one is datatype of long integer and the other is text. The
data stored for a numerial one and a text one are totally different.

Why not change the text field to a number, long integer?

Alternately I think you can create a query of the table that has the text
field and output the field as a number field.
NumField: CLng([YourFieldName])
Then link your autonumber table to the query.

Hi All,

[quoted text clipped - 4 lines]

Matt

 

[Rick Brandt]

Hi All,

I have 2 tables that I need to link the AutoNumber to a Text Field in
another table. Is this possible?

If so, how do I do it?

SELECT *
FROM Table1 INNER JOIN Table2
ON CStr(Table1.AutoNumberField) = Table2.TextField

You will only be able to write the above query in SQL view and it will not be
very efficient as the index on the AutoNumber field cannot be utilized, but it
will work.