UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ignore Number Stored As Text Error?, Access 2016    
 
   
doctor9
post Jan 12 2018, 02:47 PM
Post#1


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


So, when you want to store something like a zip code in an Excel cell as text, it pops up the little green warning triangle in the cell, saying you're storing a number as text. So you click on the "Ignore Error" option and the warning goes away.

I'm using VBA in a database to create a new Excel file from scratch. There are a couple of columns that have numbers intentionally stored as text (they're tank numbers, which sometimes are just are a number and sometimes have a letter prefix). I'd like to use VBA to tell Excel to ignore these "errors" so when the user sees the final Excel file, they won't see a bunch of green triangles.

Any suggestions?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
theDBguy
post Jan 12 2018, 02:49 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,707
Joined: 19-June 07
From: SunnySandyEggo


Hi Dennis,

Just a suggestion but have you tried storing the values preceded with an apostrophe?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jan 12 2018, 03:45 PM
Post#3


UA Admin
Posts: 33,009
Joined: 20-June 02
From: Newcastle, WA


I wonder if there's an option you can set to globally ignore errors?


--------------------
Go to the top of the page
 
doctor9
post Jan 12 2018, 03:58 PM
Post#4


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


theDBGuy,

I've tried adding an apostrophe, and I've tried formatting the cell as Text instead of General. Excel just seems to want to remind me that my numeric tank numbers aren't being stored as numbers.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GroverParkGeorge
post Jan 12 2018, 04:34 PM
Post#5


UA Admin
Posts: 33,009
Joined: 20-June 02
From: Newcastle, WA


Does this allow you to do what you want to do?
Attached File  optionsforexcel.jpg ( 146.25K )Number of downloads: 5

--------------------
Go to the top of the page
 
doctor9
post Jan 12 2018, 05:07 PM
Post#6


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


George,

I think I'll just have to leave it be. I don't want to change people's settings - I just want the numbers-as-text to be ignored in this one file.

Not a biggie.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Jan 12 2018, 05:33 PM
Post#7


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


You can do it from code - give me a few minutes to try and remember how!

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 12 2018, 05:45 PM
Post#8


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


For the cells you wish to cancel the green triangle use code like:
CODE
  ThisWorkbook.Worksheets("Sheet1").Cells(1,1).Errors(xlNumberAsText).Ignore = True

or
CODE
  ws.Range("A1").Errors(xlNumberAsText).Ignore = True


This operates at the cellular level (I'll just get my coat wary.gif ) - meaning, you are not changing anyone's settings. thumbup.gif

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
doctor9
post Jan 15 2018, 09:18 AM
Post#9


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


David,

Missed your post because I was busy scraping ice off of my windshield and shoveling snow around my car so I could go home. smile.gif

That's amazing. I poked and prodded for the right syntax and I wasn't even in the ballpark.

Thanks!

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Jan 15 2018, 09:34 AM
Post#10


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


Dennis,

yw.gif

I stumbled upon this a while back when trying to find a way to trap a different type of error in Excel.

I was playing around without success for some time before I realised this was referring to the little green triangles.

In the end I never did find a direct solution to my original problem and approached it from a different direction, so learning about this was the only positive to take away from the time spent.

Glad to have an opportunity to share it!

Good luck with the snow, stay safe!

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 10:40 PM