Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Atlanta, Georgia, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Joining files where data types are mis-match(2000)

    I am attempting to join two linked files where the join fields are different types. One is numeric the other is alpha. Thanks for any help!

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Joining files where data types are mis-match(2000)

    You can't directly join 2 tables unless the datatypes of the linking fields are the same. You can somewhat get around this for creating queries based on the 2 tables by creating a query based on the one table in which you take all of the fields and add a new field which converts the field to the other datatype. then you can link the 2nd table to this query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining files where data types are mis-match(2000)

    Linked tables can get a little nasty in this regard. Two options that I've used are (1) make local copies of the linked tables using one of the conversion functions to change data types, then base your query on the local table copies. You can look up conversion functions in Help, but the two I most often make use of are CStr to convert a numeric data type to a string, or one of the string-to-numeric conversion functions like CLng or CDbl. (2) Create the query based on the linked tables in the visual interface just as though everything were OK, then check out the SQL view and edit the JOIN clause. For example, you'd change

    <pre>SELECT Table1.*, Table2.*
    FROM Table1
    LEFT JOIN Table2 ON Table1.Field = Table2.Field</pre>

    to
    <pre>SELECT Table1.*, Table2.*
    FROM Table1
    LEFT JOIN Table2 ON CStr(Table1.Field) = CStr(Table2.Field)</pre>


    This is nice as you don't have to make local copies of the tables, but once you alter the JOIN clause the visual interface doesn't know how to represent the join graphically, so you're stuck in the SQL view. Best of luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Joining files where data types are mis-match(2000)

    Still another option, although it may be slower, is to create an implied join in your query. Add both tables in the query design and don't try to join them. Add a calculated field to the query grid that uses a conversion function to convert one of the values to the datatype of the other and in the criteria line, put a reference to the other table and field.
    Attached Images Attached Images
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •