[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


can i force join even with data type mismatch?

Posted on 2011-10-25
Medium Priority
Last Modified: 2012-05-12
i have an access table with an autonumber field used to identify our parts.

we export this list and send it to our customers for them to place orders with us.

when we receive their orders via text file, we import it into access and our original autonumber is now a text field instead of a number field.

i want to join our table to their orders and join our autonumber field with the corresponding incoming text field in order to easily match part numbers and pricing, etc...

but since it's a different data type i keep getting the error.

how can i join our number field with the text field?

Question by:intsup
LVL 61

Accepted Solution

mbizup earned 668 total points
ID: 37029023
In your join clause, try wrapping the text field in a CLng() function :


Author Comment

ID: 37029112

thanks for the input.  can you help me with the syntax of a join query using that function?

say i have my table called mytable!myautonumber and i have our customers table custtable!theirtext

how would i jiong [myautonumber] to the clng(theirtext) ?

LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 668 total points
ID: 37029154
This is the basic idea:

SELECT Table1.*, Table2.FIELD1
FROM Table1 INNER JOIN Table2 ON Table1.ID = CLng(Table2.ID);

However, you will *not* be able to view this SQL in the query Design View ... only in the SQL view.

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 61

Expert Comment

ID: 37029891

I missed your comment last night -sleep took priority :)

MX gave you the correct syntax for working CLng into a join clause in your query.

An atlernative to this approach is to create a table having the correct field types (ie: a numeric field for importing the authonumber which has been showing up as a text field), and import your text file into that existing table.  If you use that approach, the autonumber will import as a number, and you can simply join the two fields without the CLng to convert the datatype.

Both approaches should work.
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 664 total points
ID: 37030353
Although I use non-equi joins (as mentioned by mbizup and demonstrated by MX) fairly regularly when working with other peoples improperly formatted data, I prefer to use the technique mbizup recommends in her last post when importing Excel data.  It just makes more sense to create this "staging" table, and import the data into that table each time I import from Excel.

From there, I check for invalid records (those missing a required field) and import only those that are complete into my destination table.

Author Comment

ID: 37033355
thanks for the info!  it worked, they actually all did and i just modified them to meet my needs.   chose to go the query route instead of a new table..

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question