Solved

Leading plus sign in text field getting dropped

Posted on 2014-03-24
4
375 Views
Last Modified: 2014-03-24
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
Comment
Question by:dsoderstrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39950663
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
 

Author Comment

by:dsoderstrom
ID: 39950843
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39950940
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
 

Author Closing Comment

by:dsoderstrom
ID: 39951231
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 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS ACCESS VBA FORMATTING 9 63
T-SQL: How to append a column for serialized JSON data? 2 51
GA Ribbon creator 9 64
MS Access Duplicate Data Assistance 9 28
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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