Jose C
asked on
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?
thanks!
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?
thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
intsup,
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
ASKER
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) ?
thanks!