Update Query that converts a Number into it's 1/100th

[SealM]

Hi everyone, this may be my silliest question yet, but nonetheless, I cannot figure this one out:

In the table RAW, I have a field "cost_of_call". This is a number, in cents, and I want to convert it to it's number in dollars. In Excel, this would be an easy calculation, but in Access, when I try to perform it, it returns the average whole number when I try to multiply by 0.01.

here are my example numbers:
23


55
506
3025

I need an UPDATE query that will turn these numbers into:
0.23
0.55
5.06
30.25

All I get when I run this query: UPDATE Raw SET Raw.cost_of_call = Format([cost_of_call]*0.01,"Fixed");
0
1
5
30

Can someone please tell me what the query needs to be to return values with 2 decimal points? Thanks!
Mike

[pbaldy]

I doubt it's the query. What's the data type of that field in the table?

[SealM]

long integer, format is blank, decimal is 'auto' (although I changed this to two and re-ran the query and it still gives the approximate nearest whole number). Everything else is blank, not requiried, non-indexed...

Now that I look at this, I can go to the table in datasheet view, and manually change 11 to 11.1, and as soon as I tab off of it, it goes right back to 11. So these 'long integer' fields act like they cannot hold a decimal value.

[SealM]

Bah.. I change the field type to "double" and it holds a decimal just fine. But, how can I either create a "double" field to hold this data or convert an existing field using a query? This field is created when data is imported into a table, and it defaults to a long integer field. Is there a way to make a long integer field into a double field using an update query?

And thanks for helping me, btw! =)

[pbaldy]

Integer fields don't just act like they cannot hold a decimal value...they can't.

You can use your same expression in a SELECT query to display the value properly. If you want to actually change what's in the table, you'll need to change the data type or add a new field. Either can be done with a DDL query (ALTER TABLE...)

[SealM]

Okay, since I'm a noob still, I had to look it up. This works just fine to change my field named "cost_of_call" from long integer to double:

ALTER TABLE Raw
alter column cost_of_call double;

I only post this for posterity, and to help future instances of myself or those like me with this solution. Thanks!!

[pbaldy]

Happy to help! If it makes you feel any better, I would have had to look it up too, to get the syntax exactly right.