Link to home
Start Free TrialLog in
Avatar of vsllc
vsllc

asked on

Type Mismatch

I have a table with an autonumber field called VenId.  The id is 10 char and looks like 0000000001.  I want to join two tables together by this VenId field.  Issue is, the VenId field in the second table is formatted as text (to hold the leading zeros) so I'm getting a TypeMismatch error.  I tried changing the VenId field to Number, but when I do this, the leading zeros in the id disappear.  What's the best way to solve this?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

so you already know that to join two fields, they must be of the same data type.

if you want to see the leading zero, you can always do that in a query

select format([id],"0000000000") as myID from table
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or

select *
from table1 a left join table2 b on CLng(a.id)=b.id

Avatar of vsllc
vsllc

ASKER

This worked.  Thanks!