ACCESS SQL join on data type int to text

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?
StacyDAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
Nathan RileyFounderCommented:
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
 
StacyDAuthor Commented:
I'm going to try both as soon as my database finishes compacting.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Nathan RileyFounderCommented:
any luck?
0
 
StacyDAuthor Commented:
neither one worked - I ended up changing the data type.
0
 
StacyDAuthor Commented:
It pointed me to use clng and cstr.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.