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

x
?
Solved

ACCESS SQL join on data type int to text

Posted on 2008-11-14
6
Medium Priority
?
602 Views
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?
0
Comment
Question by:StacyD
  • 3
  • 2
6 Comments
 
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

0
 
LVL 65

Accepted Solution

by:
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.
0
 

Author Comment

by:StacyD
ID: 22962498
I'm going to try both as soon as my database finishes compacting.
0
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?
0
 

Author Comment

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

Author Closing Comment

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

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