Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Convert string into number during join

    Hi: When i run my query it join with Mid(tpr.AccountNum,1,5) which is like that 00000 is it possible before i join thru mid i will convert the AccountNum into number as ti.tech is an integer in my database of same size.

    Thank You.

    SELECT ti.CORP, ti.TECH, ti.TECHCONT, tj.LstVldTech, [tpr].[Status] AS Expr1, tpr1.TicketNum, tpr1.AccountNum
    FROM ([select tpr.*, Mid(tpr.AccountNum,1,5) as tprCorp from tbl_PPVResearch AS tpr]. AS tpr1 INNER JOIN tech_id AS ti ON tpr1.tprCorp = ti.CORP) INNER JOIN tbl_validDisputes AS tj ON ti.TECH = tj.LstVldTech
    WHERE ((([tpr].[Status])="Complete") AND ((tpr1.TicketNum)=[tj].[ticketnum]));

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    it is an indication of a possibly bad design having the "same" columns with different data types throughoput your database.

    But anyway - Access should convert these implicitly but you could do this explicitly with CInt(MyTextCol)

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    So I'm in this same situation. When I try to join on the columns I get "type mismatch" as an error. When I try to use CInt, I get "Join expression not supported"

    Here is my code:

    SELECT ASSET_TAG, Q.ID, ASSET_NAME, SERIAL_NUMBER, Q.STATUS, MANUFACTURER, PRODUCT_NAME, CA.CASE_ID, C.CASE_REFERENCE, C.OPERATION_NAME, C.CASE_DESCRIPTION, CON.COMPANY, EX.EXHIBIT_REFERENCE
    FROM ((((DBO_EQUIPMENT_HARDWARE_QUANTITIES AS Q LEFT JOIN DBO_EQUIPMENT_HARDWARE AS H ON Q.HARDWARE_ID = H.ID) LEFT JOIN DBO_CASE_ASSETS AS CA ON CINT(CA.ASSET_ID) = Q.ID) LEFT JOIN DBO_CASES AS C ON C.ID = CA.CASE_ID) LEFT JOIN DBO_CONTACTS AS CON ON CON.ID = C.CONTACT_ID) LEFT JOIN DBO_CASE_EXHIBITS AS EX ON EX.ID = CA.EXHIBIT_ID
    ORDER BY Q.ASSET_TAG;

    I get type mismatch when CINT and it's corresponding brackets are removed. Any ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    store:-
    numeric data in a numeric datatype
    dates or times in the datetime datatype
    strings/text in a text datatype
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by healdem View Post
    yes
    store:-
    numeric data in a numeric datatype
    dates or times in the datetime datatype
    strings/text in a text datatype
    Ya, I don't have control of the database structure. But thanks for the suggestion.

    Anyone have any ideas on how to solve my issue?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    then find the person who does have control of the structure and tell em to design the schema properly
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by healdem View Post
    then find the person who does have control of the structure and tell em to design the schema properly
    There is no way for me to change the database structure, as I mentioned before, and there is no way for me to tell anyone else to change it. If you could focus on the actual question I asked it would be helpful.

    To any future people with the same issue, I'm currently using "LIKE" to compare the two fields that are text and number. My SQL knowledge isn't good enough to know if this will always work, but the limited testing I've done seems to be working.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    At what point do you get the "not supported" message? The design grid can not represent that join, so you would have to work in SQL view. I suspect you get that message when you try to switch to design view, and it would be because it can't represent the join (in other words it's "not supported"). If so, the solution is to only use SQL view.

    An option you might consider is to create a query on that table and use CInt() there to create an Integer field. In the original query, instead of joining to the table join to this new query on the Integer field.
    Paul

  9. #9
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    At what point do you get the "not supported" message? The design grid can not represent that join, so you would have to work in SQL view. I suspect you get that message when you try to switch to design view, and it would be because it can't represent the join (in other words it's "not supported"). If so, the solution is to only use SQL view.

    An option you might consider is to create a query on that table and use CInt() there to create an Integer field. In the original query, instead of joining to the table join to this new query on the Integer field.
    I'm never trying to switch to design grid, I'm trying to press the "run" button. I get the error messages after I press the 'run' button. My code is up there, but these are the only changes I make and their results.

    CA.ASSET_ID = Q.ID Error: Type mismatch
    CINT (CA.ASSET_ID) = Q.ID Error: Join operation not supported
    CA.ASSET_ID LIKE Q.ID Works without an error.

    At this point I have the "Like" fix, so it's not really an issue anymore, but it would be nice to know why that error existed in the first place.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •