How to Join Chacter Field to Numeric Field

[Racer57]

I have a database called Projects that has a Text field called Project ID. I
also have a spreadsheet called Invoices that I want to link to the database
that has a corresponding Project ID field.

I cannot join these two fields in a query because the Invoices Project ID is
numeric, and it won't join with the text Project Id from the Projects
table.

I cannot change the properties of a linked table. Is there a way to build an
expression that converts the numeric field to a text field in a query? Or
suggest nother way to join the tables?

 

[Rick Brandt]

I have a database called Projects that has a Text field called Project ID. I
also have a spreadsheet called Invoices that I want to link to the database
that has a corresponding Project ID field.

I cannot join these two fields in a query because the Invoices Project ID is
numeric, and it won't join with the text Project Id from the Projects
table.

I cannot change the properties of a linked table. Is there a way to build an
expression that converts the numeric field to a text field in a query? Or
suggest nother way to join the tables?

You can have a join like...

SELECT blah blah
FROM Table1 INNER JOIN Table2
ON CInt(Table1.TextField) = Table2.IntegerField

....you just can't build it in the query design grid. You have to do it
while in SQL view.

 

[Ted Allen]

I think you will need to do your join in sql view and
join the numeric conversion of the text field to the
numeric field or the text conversion of the numeric field
to the text field. I would lean toward converting the
text field to a number because text comparisons are less
reliable than numeric.

If you aren't familiar with working in sql view, after
you have added the tables to the query switch to sql view
and change the FROM statement such that it reads
something along the lines of:

FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.ID = CLng(Tbl2.ID)

Of course you would have to substitute the actual table
and field names, and you could change INNER JOIN to LEFT
JOIN or RIGHT JOIN if you want all records from the left
or right table and only the matches from the other (INNER
JOIN only returns records that have matching ID's in both
tables)

Hopefully that will help. If you aren't able to get it
to work post back with the SQL of your query and I will
see if I can spot the problem.

-Ted Allen

 

[Tony]

Ive had this problem....in a query I create a calculated
field that made a new field (an expression) set equal to
the text field you want to join on ......then joined on the
expression (new field) and the numeric field in the second
table.......works for me

 

[Racer57]

Table Projects contains Text Field ID
Linked spreadsheet Spending contains numeric field Projects, which
corresponds to the ID field mentioned above.

Here is the SQL statement:

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CLng(Projects.ID) = Spending.Project;

Or

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CInt(Projects.ID) = Spending.Project;

When I try to run either of the above, I get "Overflow".

 

[Ted Allen]

Hmm, do you have any text fields in the Project table
that are null (If so, you may want to use the Nz()
function inside the conversion function)? Or, do you
have any field values that exceed the max for a long
integer (a little over 2.1 billion)? If so you could use
CDbl() or Val(). Do you have non-numeric entries in the
fields (you can use the Isnumeric() function to determine
if an entry is numeric.

You may want to test with a slightly more simple query
with just the projects table to see if you are able to do
the integer conversion on a calculated field.

Some conversions that may be a little more robust are:

CDbl(Nz(Projects.ID),0)

or,

Val(Nz(Projects.ID,0))

Post back if that doesn't solve the problem (or if it
does).

-Ted Allen

-----Original Message-----
Table Projects contains Text Field ID
Linked spreadsheet Spending contains numeric field Projects, which
corresponds to the ID field mentioned above.

Here is the SQL statement:

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CLng(Projects.ID) = Spending.Project;

Or

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CInt(Projects.ID) = Spending.Project;

When I try to run either of the above, I get "Overflow".

I think you will need to do your join in sql view and
join the numeric conversion of the text field to the
numeric field or the text conversion of the numeric field
to the text field. I would lean toward converting the
text field to a number because text comparisons are less
reliable than numeric.

If you aren't familiar with working in sql view, after
you have added the tables to the query switch to sql view
and change the FROM statement such that it reads
something along the lines of:

FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.ID = CLng(Tbl2.ID)

Of course you would have to substitute the actual table
and field names, and you could change INNER JOIN to LEFT
JOIN or RIGHT JOIN if you want all records from the left
or right table and only the matches from the other (INNER
JOIN only returns records that have matching ID's in both
tables)

Hopefully that will help. If you aren't able to get it
to work post back with the SQL of your query and I will
see if I can spot the problem.

-Ted Allen field
called Project ID. I

.

 

[Gary Walter]

PMFBI, but just in case:

PRB: "Numeric Field Overflow" Error Message Occurs When You Query a Table That Is
Linked to Excel Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q815277


"Racer57" wrote

Table Projects contains Text Field ID
Linked spreadsheet Spending contains numeric field Projects, which
corresponds to the ID field mentioned above.

Here is the SQL statement:

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CLng(Projects.ID) = Spending.Project;

Or

SELECT Projects.[Proj/Misc Name], Spending.Project
FROM Projects INNER JOIN Spending ON CInt(Projects.ID) = Spending.Project;

When I try to run either of the above, I get "Overflow".

I think you will need to do your join in sql view and
join the numeric conversion of the text field to the
numeric field or the text conversion of the numeric field
to the text field. I would lean toward converting the
text field to a number because text comparisons are less
reliable than numeric.

If you aren't familiar with working in sql view, after
you have added the tables to the query switch to sql view
and change the FROM statement such that it reads
something along the lines of:

FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.ID = CLng(Tbl2.ID)

Of course you would have to substitute the actual table
and field names, and you could change INNER JOIN to LEFT
JOIN or RIGHT JOIN if you want all records from the left
or right table and only the matches from the other (INNER
JOIN only returns records that have matching ID's in both
tables)

Hopefully that will help. If you aren't able to get it
to work post back with the SQL of your query and I will
see if I can spot the problem.

-Ted Allen