Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Leading plus sign in text field getting dropped

I have an Access 2010 database that links to a table in SQL server 2008 R2 using ODBC.
One of the fields in the SQL table has a field type of varchar(30).  
Some of the records have data in this field that starts with a plus sign (ex. +354384).
When I link to the SQL table in Access, the plus sign is sometimes getting dropped.  For example, +354384 shows up as 354384.  On other records, the plus sign shows up as it should.
When I query the same data in SQL server, the plus sign shows up for all records.
Any ideas on what is going on here?
0
dsoderstrom
Asked:
dsoderstrom
  • 2
2 Solutions
 
PatHartmanCommented:
If it shows up for some but not for others, it sounds like there is actually a different character that is being rendered as a + by SQL Server.  Create a query that selects records that start with + to see if you get the set you expect.
0
 
dsoderstromAuthor Commented:
I wrote a query in Access with selection criteria for the field as "+354384" and if found the record.  But it displayed the data in the field as 354384 (no plus sign).
0
 
Scott PletcherSenior DBACommented:
It seems Access is displaying the value as if it were numeric/integer and not character.

Try explicitly specifying the column's data type in Access as varchar -- or the equivalent of varchar, if it's not called "varchar" in Access.
0
 
dsoderstromAuthor Commented:
When linking to the SQL table, the varchar fields become text type fields in the Access table, which is what they should be.  When I open the linked table in Access I see the plus sign at the beginning of the data in the field.  But, when I do a select query on the table to display the data, the plus sign gets dropped.
  However, if I run a "Make Table" query in Access to read the data from the linked table and write it to a new Access table and then do a select query on the new Access table the plus sign does not get dropped.
  Very strange.  Anyway, knowing that and having spent way to much time on this already I can design a workaround to accomplish what I need to do.  
   Thank you both for contributing.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now