Expressing a "Null" value in Excel

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
PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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
 
jppintoCommented:
You should use the formula like this:

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

jppinto
0
 
jppintoCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
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
 
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
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
 
Rory ArchibaldCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.