Solved

Expressing a "Null" value in Excel

Posted on 2008-06-16
8
1,033 Views
Last Modified: 2010-05-18
I have an Access file that has several links to Excel worksheets.  Several of the worksheets have formulas that mix numeric and text values, such as the following: '=IF(A5,5,"Alpha")'.  Excel handles mixed values with virtuosity, but when linked, Access will interpret the field as either Text or Numeric; and if the value is numeric, it will return an error for text values, and vice versa.  If I were to change the formula to '=IF(A5,5,'')', I still get an error, because a blank string is not the equivalent of a null value.  Excel, apparently does not have the equivalent of a null value, as '=IF(A5,5,Null)' produces a '#Name' error.  What can I do to eliminate the errors I'm finding in Access?
Thanks, ~Peter
0
Comment
Question by:PeterFrb
  • 2
  • 2
  • 2
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 21796695
You should use the formula like this:

=IF(A5;5;"")    use " instead of '

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 21796715
In your case you should use this:

=IF(A5,5,"")

jppinto

The use of either , or ; depends of the Region of your Office program!
0
 

Author Comment

by:PeterFrb
ID: 21796913
Thank you for the correction: I merely mistyped in my post: it was correct in Excel.  
Given that the formula is typed in correctly, my central question is how to get Excel to express a null value for Access, as a blank string and not equivalent to a null value.  
Thanks, ~Peter
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 21798516
It's a pretty common problem with mixed data types - see this post for example: http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/
You can either try and convert all the data to text, using say:
 =IF(A5,"5","")

or you can add IMEX=1 to the connection string and all mixed columns should be treated as text.
HTH
Rory
0
 

Author Comment

by:PeterFrb
ID: 21874980
Hi, Rory:
Sorry I left this thread sleep.  Your solution of putting "IMEX=1" into the connection string sounds great!  How do I do this from the standpoint of Access?  When I use Access to link to an external table, it provides me with the "Link Spreadsheet Wizard", and nowhere in the list of succeeding menus does it give me the opportunity to edit the connection string.  Am I limited to programming code as the sole means of using a connection string to import data?  
~Peter
P.S., I will explore your solution and give credit if it works.  Thanks.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21875197
I don't think you can change it without code (although you can see the IMEX property in the table properties, you can't change it there). Something like this is required:

Set tdf = DBEngine(0)(0).TableDefs("Sheet1")
tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=1")
tdf.RefreshLink

Regards,
Rory
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

679 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