If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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? |
#2
|
|||
|
|||
How to Join Chacter Field to Numeric Field
"Racer57" wrote in message
om... 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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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 -----Original Message----- 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? . |
#4
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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 |
#5
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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" wrote in message ... 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 -----Original Message----- 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? . |
#6
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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 a 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". "Ted Allen" wrote in message ... 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 -----Original Message----- 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? . . |
#7
|
|||
|
|||
How to Join Chacter Field to Numeric Field
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...;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". "Ted Allen" wrote in message ... 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 -----Original Message----- 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? . |
Thread Tools | |
Display Modes | |
|
|