• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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?
0
vsllc
Asked:
vsllc
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
0
 
HainKurtSr. System AnalystCommented:
best way is make both fileds number...
if this is not possible then use

select *
from table1 a left join table2 b on a.id=right("0000000000" & b.id,10)
0
 
HainKurtSr. System AnalystCommented:
or

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

0
 
vsllcAuthor Commented:
This worked.  Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now