can i force join even with data type mismatch?

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?

Who is Participating?
In your join clause, try wrapping the text field in a CLng() function :

intsupAuthor Commented:

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) ?

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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.
Dale FyeCommented:
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.
intsupAuthor Commented:
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..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.