Solved

Leading plus sign in text field getting dropped

Posted on 2014-03-24
4
371 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 36

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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google drive Permission Error 43 46
MS Access, hourly employee status 8 42
How can I find this data? 3 25
Trying to delete two linked tables from front-end 7 15
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 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