Solved

Expressing a "Null" value in Excel

Posted on 2008-06-16
8
1,038 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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