Results 1 to 7 of 7
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    Cant join on text fields between sql server and access

    I am trying to import data from a csv. the base import works fine but I want to add data in a couple of fields so I am trying to create a query that will join the sql server table and the access table. I join it on two fields. a car number and car initials. I am able to join on the car number but it wont let me join on the initial, which is just a text field.

    Does anyone know why this is happening.



    One interesting thing I am seeing is that in the query if I click in the Initial field the data looks blank. If I click out it shows the data. This doesn't happen on any of the other fields so It must be related to the issue somehow.

  2. #2
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Update, I dug a bit further and it appears that the sql import is importing a leading space in that text field. I was able to create a sub query and use right(initial,4) and then use that subquery in the main query and it worked. However, I cant change the field size to 4 because it will fail the import (some fields I don't need get imported and later deleted and they are longer than 4)

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What are the SQL data types involved. It sounds line Char or similar which is effectively a fixed width field?

    Use a VarChar or NVarChar instead.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I was using VarChar set to 50 so I switched it to nchar (4) and also tried VarChar(4) so that it could only import the necessary data. However, it fails with a truncate error. I believe this is because there are 3-4 blank lines at the end of the file (i dont control the way the file comes) and so unless I set the varchar value high it will not run the import.

    Both are text, it is just that for some reason the import adds a blank space on at the beginning so when I try the join it sees it as a different value of course.

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I am going to mark this as solved because the problem appears to be in the sql side, not the access side.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you have enough rights on the SQL server, try importing the file directly into SQL server. You can use a SSIS package to do this. Doing so will give you a lot of extra possibilities.

  7. #7
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Yes, I have access to the server. I have never done an SSIS package but downloaded Visual Studio and created a catalog yesterday and it does indeed seem to have a lot of possibilities, thanks for the heads up

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-26-2019, 11:53 PM
  2. Replies: 8
    Last Post: 10-30-2018, 02:51 AM
  3. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  4. Replies: 0
    Last Post: 05-21-2012, 07:00 PM
  5. INNER JOIN differences from SQL Server
    By naurispunk in forum Queries
    Replies: 0
    Last Post: 07-22-2010, 03:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums