[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


ACCESS SQL join on data type int to text

Posted on 2008-11-14
Medium Priority
Last Modified: 2012-05-05
Hello All:
In teh sql below, the data type for b.branch is text and the data type for f.office is number. I tried to run the statement below and I get data type mismatch. I am sure the "and Clng(b.branch) = f.office" portion is the trouble: when I remove it the statement runs.

select f.fa,  f.name,f.empid, f.office, b.description,f.revenue
from [Fiscal 2008] f, [Product Code Table Sept 2008] p, [September 2008 Branch Hierarchy] b
where  f.[product code] = p.[prod code]
and Clng(b.branch) = f.office
and f.empid <> ""
and f.empid not in ("99999","99970")
and p.[lvl 2] in (1,4,2)

Any suggestions?
Question by:StacyD
  • 3
  • 2
LVL 12

Expert Comment

by:Nathan Riley
ID: 22961053
Change your select to:

select f.fa,  f.name,f.empid, cast(f.office as varchar), b.description,f.revenue

Open in new window

LVL 65

Accepted Solution

rockiroads earned 225 total points
ID: 22962385
Cant see why that fails. Have you tried using VAL?

and val(b.branch) = f.office

what type of number is office defined as. Maybe need to use another number conversion func.

Author Comment

ID: 22962498
I'm going to try both as soon as my database finishes compacting.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 12

Expert Comment

by:Nathan Riley
ID: 23015714
any luck?

Author Comment

ID: 23538815
neither one worked - I ended up changing the data type.

Author Closing Comment

ID: 31516832
It pointed me to use clng and cstr.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 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