Is There Simple Way to Convert Integer to Decimal Within Same Tabl

[MJ]

I am at a loss for this issues. Getting some bad data in, one field in a
small subset of entire table should be decimal but coming in as integer. I
know that I can covert it simply by dividing by 100 and I have been able to
do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT changing
the field name? I tried using an Update query, but errors out...

UPDATE [Table1_OptEditErrors] SET ([Table1_OptEditErrors].ClmAmt/100) AS
ClmAmt
WHERE ((([Table1_OptEditErrors].Text) Like "Subscriber DOB*") AND
(([Table1_OptEditErrors].[Error Code])="SIT1") AND
(([Table1_OptEditErrors].RespDept)="DEPT1"));

"ClmAmt" is the field containing the bad data and I would like to retain the
corrected data in the same field. Please keep in mind that this is only ~3%
of the data, so there is a lot of good data present.

Anyone have any good suggestions on a simple solution?

 

[Bob Barrows [MVP]]

I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and I
have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET ([Table1_OptEditErrors].ClmAmt/100)
AS ClmAmt

wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100

 

[KARL DEWEY]

Can you not change the datatype in design view to Number - Single?

 

[MJ]

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it on
values that are already in a decimal format. Is there a good way to check
for it before doing the update?

Thanks again for your inputs.

--

MJ

I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and I
have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET ([Table1_OptEditErrors].ClmAmt/100)
AS ClmAmt

wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Nothing infallible that I can come up with on short notice here. I'll think
about it though. Is there some characteristic that you think would help
discriminate the ones that need updating from those that don't?

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it
on values that are already in a decimal format. Is there a good way
to check for it before doing the update?

Thanks again for your inputs.

I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and
I have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET
([Table1_OptEditErrors].ClmAmt/100) AS ClmAmt

wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[John W. Vinson/MVP]

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it on
values that are already in a decimal format. Is there a good way to check
for it before doing the update?

The *FORMAT* is completely irrelevant to the actual content of the
field - format only controls how that content is displayed. What is
the range of valid values? Is 1.05 a legitimate value? How about
105.00? How about 105?

 

[John Spencer]

Part of the solution would be to look for fields using this criteri

WHERE ClmAmt - Int(ClmAmt) <> 0

BUT that won't work with claim amounts that need to be changed and have
not yet been changed.
100 = 100.0 = 100.00 = 100.0000 etc
They are all stored in the database as 100 and so you can not
distinguish the 100 that has not been changed from the 100 that has been
changed.

If you can add another field to the table, you could use it to flag
those values that have been changed and use that to prevent changing
them again.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Nothing infallible that I can come up with on short notice here. I'll think
about it though. Is there some characteristic that you think would help
discriminate the ones that need updating from those that don't?

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it
on values that are already in a decimal format. Is there a good way
to check for it before doing the update?

Thanks again for your inputs.

MJ wrote:
I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and
I have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET
([Table1_OptEditErrors].ClmAmt/100) AS ClmAmt
wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[MJ]

The "bad" input comes in as WHOLE number integers (1, 23, 345, etc) that
represent monetary values. Using the examples (mention above) the results
should be: 0.01, 0.23, 3.45, etc.

If I were king for a day, I would simply have the IT guys fix the output
from their database to our database, but don't see that happening soon (I can
always hope for it though).

 

[John W. Vinson/MVP]

The "bad" input comes in as WHOLE number integers (1, 23, 345, etc) that
represent monetary values. Using the examples (mention above) the results
should be: 0.01, 0.23, 3.45, etc.

AGAIN:

What is the range of legitimate values?

If $3.45 is a legitmate value, it suggests that $3.00 is a legitimate
value. You're implying that an entry of 23 should be $0.23; ok, that's
fine - but might it ever be $23.00? *How can you tell whether to
divide by 100 or not*? Can you be absolutely certain that you'll never
have a sale that is an integer number of dollars?

So: is an entry of 3 *always* going to be 3 cents? Or might it
sometimes be 3 dollars?

 

[MJ]

It can be any value, routinely have figures exceeding 100,000.00. Doesn't
necessarily end in even dollars. The figures come in looking like: 32156
that should be 321.56; 423857 should be 4,238.57; and 4578900 would be
45,789.00. I hope that this clarifies the question about legitimate values.
Please let me know if there are any other questions.

 

[John W. Vinson/MVP]

It can be any value, routinely have figures exceeding 100,000.00. Doesn't
necessarily end in even dollars. The figures come in looking like: 32156
that should be 321.56; 423857 should be 4,238.57; and 4578900 would be
45,789.00. I hope that this clarifies the question about legitimate values.
Please let me know if there are any other questions.

And 31256.00 should be 31256? Or should it be 3125600?

If the incoming data is in a text file, or a Text field in a table,
you could certainly use a criterion of

NOT LIKE "*.*"

to find records where the field does not contain a decimal point.
However, if the field is numeric, then I can't imagine any way, even
in principle, to distinguish legitimate integer values from erroneous
integer values.

 

[MJ]

John,

Now that you mention it, the input files are txt files. The NOT Like "*.*"
worked like a charm except for 1 form.

The field being fixed is a Number (Double) with two decimal places.

Records that happen to end in 00 won't show "*.00" after the update. Unless
you have a better idea for those times, the simplest solution might well be a
combination of the earlier suggestion and John Spencer's idea of a NEW Update
field which would be set when the record is updated and checked during
subsequent updates.

Thank you for your time and assistance.

 

[MJ]

John,

Your idea of a NEW field would work. I will talk with my partner on this
issue and see if it will work for him.

Thank you for your time and assistance.

--

MJ

Part of the solution would be to look for fields using this criteri

WHERE ClmAmt - Int(ClmAmt) <> 0

BUT that won't work with claim amounts that need to be changed and have
not yet been changed.
100 = 100.0 = 100.00 = 100.0000 etc
They are all stored in the database as 100 and so you can not
distinguish the 100 that has not been changed from the 100 that has been
changed.

If you can add another field to the table, you could use it to flag
those values that have been changed and use that to prevent changing
them again.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Nothing infallible that I can come up with on short notice here. I'll think
about it though. Is there some characteristic that you think would help
discriminate the ones that need updating from those that don't?

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it
on values that are already in a decimal format. Is there a good way
to check for it before doing the update?

Thanks again for your inputs.


MJ wrote:
I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and
I have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET
([Table1_OptEditErrors].ClmAmt/100) AS ClmAmt
wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[MJ]

John,

Your suggestion was just the ticket to fix our problem! Thanks again.
--

MJ

Part of the solution would be to look for fields using this criteri

WHERE ClmAmt - Int(ClmAmt) <> 0

BUT that won't work with claim amounts that need to be changed and have
not yet been changed.
100 = 100.0 = 100.00 = 100.0000 etc
They are all stored in the database as 100 and so you can not
distinguish the 100 that has not been changed from the 100 that has been
changed.

If you can add another field to the table, you could use it to flag
those values that have been changed and use that to prevent changing
them again.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Nothing infallible that I can come up with on short notice here. I'll think
about it though. Is there some characteristic that you think would help
discriminate the ones that need updating from those that don't?

It works great, thank you.

Now a new wrinkle, once I make that update I do not want to repeat it
on values that are already in a decimal format. Is there a good way
to check for it before doing the update?

Thanks again for your inputs.


MJ wrote:
I am at a loss for this issues. Getting some bad data in, one field
in a small subset of entire table should be decimal but coming in as
integer. I know that I can covert it simply by dividing by 100 and
I have been able to do a Make Table query.

Is there a simple way to do this within the existing table WITHOUT
changing the field name? I tried using an Update query, but errors
out...

UPDATE [Table1_OptEditErrors] SET
([Table1_OptEditErrors].ClmAmt/100) AS ClmAmt
wrong syntax. it should be:

SET [ClmAmt] = [ClmAmt]/100




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"