ACCESS SQL join on data type int to text

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

select f.fa,,f.empid,, 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) =
and f.empid <> ""
and f.empid not in ("99999","99970")
and p.[lvl 2] in (1,4,2)

Any suggestions?
Question by:StacyD
    LVL 11

    Expert Comment

    by:N R
    Change your select to:

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

    Open in new window

    LVL 65

    Accepted Solution

    Cant see why that fails. Have you tried using VAL?

    and val(b.branch) =

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

    Author Comment

    I'm going to try both as soon as my database finishes compacting.
    LVL 11

    Expert Comment

    by:N R
    any luck?

    Author Comment

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

    Author Closing Comment

    It pointed me to use clng and cstr.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now