How to join on two disparate keys (one is string, other is int)?

Posted on 2009-07-16
Last Modified: 2012-08-14
I have two tables in MS Access that are suppose to share a common key...however the number in one is actually stored as a string while the other is stored as an int.

I think I want to use the convert() function...

Select blah1.key, blah2.key
from blah1
ON blah1.key = blah2.key

How do I use the convert() or something like it for the join?
Question by:M3hcSS
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24873399
Either use CInt(strColumnName) or CStr(intColumnName).
Select blah1.key, blah2.key
from blah1
ON blah1.key = CStr(blah2.key)
Select blah1.key, blah2.key
from blah1
ON CInt(blah1.key) = blah2.key

Open in new window

LVL 30

Expert Comment

ID: 24874244
You may need to create a query of any table changing the format of the relevant field.
Then join the query with the other table.

Accepted Solution

M3hcSS earned 0 total points
ID: 24938603
Solution was

JOIN Table2 ON Table2.CityList LIKE '%' + Table1.City + '%'

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formatting Excel from Access with VBA... 5 57
Access Database 5 40
Copy all value in one form to another 4 39
Convert VBA UDF to SQl SERVER UDF 4 46
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 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