Solved

Leading plus sign in text field getting dropped

Posted on 2014-03-24
4
376 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 38

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

635 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