can i force join even with data type mismatch?

Posted on 2011-10-25
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

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


    Author Comment


    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 - Access MVP)
    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.

    LVL 61

    Expert Comment


    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 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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

    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

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now