• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • Last Modified:

How do I get Access to "not" treat blank cells as null.

How do I get Access to "not" treat blank cells as null.  I have a customer who copiees data from Access to Excel and when he sends it over, the cells are skewed so that when he geos to do an Scatter chart to compare, it does not show a relationship. We are able to change the cells in Excel, but he would like to know if there is a way fix this in Access so that he does not have to make the adjustment in Excel.
0
CPChem1
Asked:
CPChem1
  • 3
  • 3
  • 3
  • +2
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
In what way do you "change" them in Excel?

Set them to what?
Perhaps a query column as
IIF([YourField] Is Null, " ",[YourField])
0
 
Richard DanekeCommented:
Without concern on field type, you could also use:
FieldName: NZ(FieldName, " ")  for text and
FieldName: NZ(FieldName, 0) for numbers
The NZ provides a substitute for a variant field when Null
0
 
CPChem1Author Commented:
LPurvis,

He is doing a query Using Edit to select all then copy then paste into Excel.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Jim P.Commented:
Set the defaults for the fields in the table.  Set them to empty strings or zeroes
0
 
CPChem1Author Commented:
jimpen,

how do I Set the defaults for the fields in the table and set them to empty strings or zeroes? :-)
thanks!  Is ti the same as one of the examples above?  I want something short and simple.
0
 
Jim P.Commented:
Go into the table design view and for each column (skip the autonumber) on the bottom of the sheet it will have a default value field.  If it's a text put in  "" if numeric field the a 0.
0
 
Leigh PurvisDatabase DeveloperCommented:
The only thing I'd say is that you mention this is done from a query - so at that stage it is an opportunity to make sure that the data is how you want it to be.
(A default value from the record's creation may not still be present).
Just a thought.
0
 
Richard DanekeCommented:
The original IIF test or the NZ function will work.  As you copy and paste to Excel, numbers may look better if you format them in the properties of your query - currency or fixed decimals.
0
 
Jim P.Commented:
>> The original IIF test or the NZ function will work.  

Then you have to edit every query to do it.  By setting default values in for the tables it will clear the problem.  He will have to go back and run update queries for existing data, but that is a  one time fix after he sets defaults.

UPDATE TableName
SET MyTextFld = ""
WHERE MyTextFld IS NULL

UPDATE TableName
SET MyNumFld = 0
WHERE MyNumFld IS NULL
0
 
Richard DanekeCommented:
jimpen,
You are correct, the function would have to be in a query.
If the user is only using a limited number of queries to output to Excel, the Author can decide if the work is acceptable.
I humbly bow out of the debate.
0
 
Jeffrey CoachmanCommented:
CPChem1,

Just to jump in here...

In excel you can choose how you want empty cells to graph.

Click: Tools-->Options
Then click the Chart tab
Then Chooes an option for: Plot empty cells as:
Not Plotted, Zero, or Interpolated

This way you can do away with all the Access stuff, and just use Excel. This might be simpler.

Hope this helps as well
0
 
CPChem1Author Commented:
This is what we used....thanks so much to all!

In excel you can choose how you want empty cells to graph.

Click: Tools-->Options
Then click the Chart tab
Then Chooes an option for: Plot empty cells as:
Not Plotted, Zero, or Interpolated

This way you can do away with all the Access stuff, and just use Excel. This might be simpler.

Hope this helps as well
0
 
Jeffrey CoachmanCommented:
Glad I could help!
:)

Thanks for the grade
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now