How to catch numeric overflow data?

[Stella740pl]

Hi!

I've received a table of about 50 fields & close to 1000 records. Quite a lot of them contain numeric overflow data in about a dozen fields. I would like to be able to select/count/display them for different purposes, and then, possibly, replace overflown fields with a valid value.

Is there a way to catch them all? Is there a function, trick or something else that would help me to do that?

Thank you.
Stella

 

[ramani]

Hi Stella

BROW FOR "*" $ STR(c1)

OR

LIST FOR "*" $ STR(c1)



____________________________________________
Ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[Stella740pl]

Thank you, Ramani!
It's so simple after someone shows you how!
I didn't think that STR() of overflown field will still have * in it.
Stella.

 

[dbMark]

One caution, though. If you BROWSE the screen, do not hit ENTER on that field. Normally, hitting ENTER on an untouched field just takes you to the next field without changing the contents, but not in this case! If you hit the ENTER key on a field with a numeric overflow it will yipes! replace the ***** with a totally normal-looking zero value! So beware of that behavior which could end up converting/hiding bad data.

 

[Stella740pl]

dbMark,
Thanks for the warning. I was actually done with that table by now, it turned out that all the overflown fields could be recalculated from good ones (it was a file for running a report on it) after widening them a little, so that's what I did. But I actually tried to replicate the behavior on a copy I left in the original state, and couldn't. After hitting ENTER all * stayed *. I know in Excel you could see the actual number in the formula field when the cell is too narrow and shows #. But I don't recall seeing this behavior in VFP. Can you specify, please, what else could be contributing to this?
Stella

 

[dbMark]

I guess if you opened the table NOUPDATE it wouldn't allow any changes. Also, if the VFP table's field was character type it would not try to re-evalute the field if you hit ENTER, but then I doubt in that case you'd have had the numeric overflow issue when appending...

Here's how I demonstrate the behavior:

CREATE TABLE test1 (field1 n(5,2))
APPEND BLANK
REPLACE field1 WITH 12.34  && field filled
APPEND BLANK
REPLACE field1 WITH 123.45  && rightmost number lost but no error
APPEND BLANK
REPLACE field1 WITH 12345.67 && decimals lost but no error
APPEND BLANK
REPLACE field1 WITH 123456  && numeric overflow error
BROWSE
*USE IN test1
*ERASE test1.dbf

 

[dbMark]

Regarding the differences between Excel and Visual FoxPro, Excel does have a formatted display separate from the data contents of the cell. But FoxPro's numeric field is actually a character field that is interpreted as numeric within the dbatbase environment. If you used a disk editor and looked at the raw data for the numeric fields in my example above you would see simple text such as 12.34 or 123.4 or 12345 or ***** if overflowed. If the field contains asterisks due to numeric overflow, then that's all that's there. Nothing is "behind" it.

 

[Stella740pl]

dbMark,

I got it.
It behaves like this when open exclusively.

I didn't notice what was the difference at first. I ran your test, and a dozen more (I created a table with a select statement, I replaced the value with division by 0, etc.), and the table behaves the way you describe all the time. I open mine in the same environment, and it doesn't. The field is numeric, and it lets me type a number in it, but doesn't change when ENTER is pressed. Until I reopened it exclusively. Well, thanks for pointing out, it is good to keep in mind. Actually, I guess I've seen this behavior before (just forgot about it), probably that's why I didn't think that STR() of overflown field would still have * in it.

Stella.

 

[dbMark]

Well, what do you know, I didn't try it shared. I learn something new each day. If you try RLOCK() first, then ENTER will change the field even when opened shared because the record is locked.

What a tangled, tangled web we weave if first we try to... program!

 

[Stella740pl]

dbMark,

>I learn something new each day.
I guess, everyone does. Say, I didn't try to RLOCK() it first, now I will know. Thanks for the ideas.