Re: Is There Simple Way to Convert Integer to Decimal Within Same

John,

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

MJ


"John Spencer" wrote:

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
'====================================================


Bob Barrows [MVP] wrote:
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?

MJ wrote:
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"


.

Relevant Pages

  • Re: Is There Simple Way to Convert Integer to Decimal Within Same
    ... "John Spencer" wrote: ... Access MVP 2002-2005, 2007-2008 ... I tried using an Update query, ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Is There Simple Way to Convert Integer to Decimal Within Same
    ... Access MVP 2002-2005, 2007-2008 ... on values that are already in a decimal format. ... I tried using an Update query, ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Update 1 column to many column
    ... "John Spencer (MVP)" wrote: ... > WHERE Race is Not Null ... >> How can I write in Update query? ...
    (microsoft.public.access.queries)
  • Re: Inserting blank columns into query
    ... In the query grid, I would probably do either this: ... John Spencer wrote: ... Access MVP 2002-2005, 2007-2008 ... This email account is my spam trap so I ...
    (microsoft.public.access.queries)
  • Re: find/replace characters like quotes
    ... {"this is a text and the special characters at the beginning and at the end ... "John Spencer" wrote: ... Then all you need to do is run an update query that looks like the following ... Set rst =.OpenRecordset ...
    (microsoft.public.access.queries)
Loading