Solved

Expressing a "Null" value in Excel

Posted on 2008-06-16
8
1,031 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

791 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