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.
CPChem1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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])
Richard DanekeTrainerCommented:
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
CPChem1Author Commented:
LPurvis,

He is doing a query Using Edit to select all then copy then paste into Excel.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jim P.Commented:
Set the defaults for the fields in the table.  Set them to empty strings or zeroes
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.
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.
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.
Richard DanekeTrainerCommented:
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.
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
Richard DanekeTrainerCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help!
:)

Thanks for the grade
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.