Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Expressing a "Null" value in Excel

Posted on 2008-06-16
8
Medium Priority
?
1,043 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 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