473,544 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Best way to store calc result that can be "NA"?

I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

?
Nov 13 '05 #1
16 1724
On Tue, 22 Feb 2005 15:38:55 -0500, PeteCresswell <x@y.z> wrote:
staged beforehand via a batch job

I should add that right now it looks like these fields are the final
result tb used on various reports. i.e. they do not have to be
summed....(cros sed fingers....)
Nov 13 '05 #2
And, while I'm at it...

Is there any downside to storing amounts as percentages instead of
storing them as decimals and changing to percent for display.

I've always stored as decimal numbers and comverted...but somebody
thinks it would be better to store as percent.....
Nov 13 '05 #3
PeteCresswell wrote:
I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields. My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.


I am definitely not a greater mind than yours with respect to Access,
but what about...

A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases. theory!!!!) with the
relational way of doing things (ie, not storing nulls as data).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4
Why don't you add a 2nd field to show status of the calculated field? That
way you can note among other things that the calculated value is now invalid
because the input values have changed.

"PeteCressw ell" <x@y.z> wrote in message
news:fq******** *************** *********@4ax.c om...
I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

?

Nov 13 '05 #5
On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> wrote:
A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases. theory!


But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.
Nov 13 '05 #6
One more (unrelated) thing:

Is it just me, or are there "Re" literals proliferating in my replies?

My current subject line looks like:

"Re: Re: Re: Best way to store calc result that can be "NA"?"

Some setting in my newsreader (Agent)?
Nov 13 '05 #7
If you have a defined range for the values that are "acceptable " for that
field (say -9999 to +9999), you could use a value that is way out of range
(-99999999) and then do a test for a value that is less than -99999990 and
use that to say that the Calc couldn't do the job.

Otherwise, using a flag field would be a reasonable solution.

--

Ken Snell
<MS ACCESS MVP>

"PeteCressw ell" <x@y.z.Invali d> wrote in message
news:lt******** *************** *********@4ax.c om...
On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> wrote:
A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases. theory!


But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.

Nov 13 '05 #8
PeteCresswell wrote:
On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> wrote:
A null would be a result of the bad calculation you mentioned. I assumeyou're working in Jet, which allows you to update a double to a null.
Null means no value, which is in keeping with an error or insufficientdata and could act as a flag for bad calcs and still be in keeping (Ithink, better not post this on comp.databases. theory!


But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.


I haven't been this route before. Plus, I'm not a greater Access mind.
How about leaving the field as Decimal and using, say, -10000 for
recalcitrant situations? Then convert -10000 to "(NA)" at the output
stage maybe with a function that returns CStr(Nz([CalcField],"")) or
"(NA)". Use "WHERE [CalcField] <> -10000" if the values need to be
summed?

James A. Fortune

Nov 13 '05 #9
PeteCresswell wrote:
I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.


You could use

Public Function Indeterminate() As Double
On Error Resume Next
Indeterminate = 1E+308
Indeterminate = Indeterminate + 1E+308
Indeterminate = Indeterminate * 0
End Function

to return a number that should display as

-1.#IND

(This function should be credited primarily to Vincent Quesnoit.)

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3038
by: Berthold Hoellmann | last post by:
Hello, When I use ./configure --with-thread --with-fpectl --with-signal-module \ --with-pymalloc --enable-shared --with-cxx=g++ make test on 2.3.3 I get
14
6628
by: Vilmar Brazão de Oliveira | last post by:
Hi all, I was using: <% Set objWshell = Server.CreateObject("WScript.Shell") objWshell.Run "%COMSPEC% /C ren topoX.jpg topo_cliente.jpg", 0, TRUE %> BUT the comand COMSPEC disapeared of all my machines which we have ms-windows 2000 installed! So now no-one machine can continue run my routines which accessed ms-dos by ASP
235
11509
by: napi | last post by:
I think you would agree with me that a C compiler that directly produces Java Byte Code to be run on any JVM is something that is missing to software programmers so far. With such a tool one could stay with C and still be able to produce Java byte code for platform independent apps. Also, old programs (with some tweaking) could be...
9
3489
by: Anders Borum | last post by:
Hello! I have a class that needs to validate the input value, when a programmer changes a specific property on a class. The input should only accept the following pattern {1,n} (alpha-numeric with atleast one entry). I'm a little resistant to implementing a regular expression validation, because of the overhead involved (not because I...
5
1947
by: Rob | last post by:
In many articles related to VB.net the word "class" is used... How many meanings are there to this word ? "possible to derived a class from another" "forms are full-fledged classes" "base class"
2
3801
by: Rafał Maj Raf256 | last post by:
How can I manually (other then http://www.boost.org/libs/smart_ptr/enable_shared_from_this.html) create a shared, and a weak pointer to "this"? Of course I dont want the shared / weak pointer to cause object to be deleted when shared/weak ptr will go out of scope. -- Wymiana starych układów... na nowe układy - prawie jak walka z...
0
3560
by: André | last post by:
Hi, Still the same (unsolved problem), but other error now. I have a detailsview for inserting data, and a dropdownlist which selectedValue must be used to fill one of the field of the detailsview. Table 'pc' is related to table 'lok'. I tried a lot of things, but still without succes. Here are the two methods i tried with two different...
3
3272
by: Aaron | last post by:
I'm trying to parse a table on a webpage to pull down some data I need. The page is based off of information entered into a form. when you submit the data from the form it displays a "Searching..." page then, refreshes and displays the table I want. I have code that grabs data from the page using cURL but when I look at the data it contains...
8
2051
by: Freddy Coal | last post by:
Hi, I would like make a search and replace in a file, for that I need get the text in a textbox, but I need recognyze literally characters of the user like a commands, for example: The chain of the user is: "Hello world" & chr(64) & vbcrlf If I capture the textbox, I get: ""Hello world" & chr(64) & vbcrlf" But I would like get the...
0
7370
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7387
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7714
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5920
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4924
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3424
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3415
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
992
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.