Decimal Places

  • Thread starter Thread starter Wendy L
  • Start date Start date
W

Wendy L

My Access help file is buggy and keeps giving me an error when I try to
access it. So I thought I would ask a very simple question here.

I'm running Windows XP with Office XP.

How do you set decimals into a field in a table? I need a decimal placed two
digits to the left of the last character in my field. The field is imported
as a Long Integer.
 
Bytes, Integers and Longs are whole numbers, singles and doubles, currency
and decimal have decimal places.

So you can't have a decimal place in your Long Integer.

Look up "Data Type Summary" in Help.

Mich
 
You need to store the data in a field type that accepts decimal fractions,
such as Single, Double, Currency or Decimal. Then just divide the existing
values by 100.
 
What statement do I need to use to divide by 100? And where does that
statement need to be placed in Design View of the table? In the Format part?
 
I get the impression, Wendy, that you're more accustomed to working with
spreadsheets than with relational databases, yes? If so, it's important to
realise that while an Access table may look superficially somewhat similar
to a spreadsheet, it is actually very different. A spreadsheet is used to
store, manipulate and display data. A table in a relational database is used
*solely* to store the data. Manipulation and display are the work of other
objects - queries, forms, reports and code.

If you want to permanently change the values stored in the table, then
(after changing the field type) run an update query such as the following.
Note that if you import data on an on-going basis, you need to take care not
to run this query more than once on the same data.

UPDATE tblTest SET tblTest.TestDouble = [TestDouble]/100;

If you want to leave the data as it is and merely *display* it with two
decimal places, you can use the following expression in a query:

SELECT [TestDouble]/100 AS FormattedData
FROM tblTest;

('FormattedData' is just a label - an 'alias' in database terminology - for
the calculated column - it can be anything you like, provided it abides by
the Access naming rules).

Similarly, you can use an expression such as the following in the
ControlSource property of a text box on a form or report:

=[TestDouble]/100

In all of these examples, replace 'TestDouble' with the name of your field,
and 'tblTest' with the name of your table.
 
1) (optional) Make a backup of either your entire db or just the table
2) In Design view of the table, change the field type from
Number/LongInteger to Number/Single
3) Create a new query in Design view.
-Add your table to it.
-Add [TheFieldIWantToModify] to the QueryByExample grid.
-Change the QueryType to "Update Query" (there is a drop-down control in
the toolbar).
-In "Update To" under [TheFieldIWantToModify],
enter "[TheFieldIWantToModify] * .01"
(WITHOUT the quotes but WITH the brackets)
4) Run the query (the big Red !)

Hope this helps,
 
Thank you, George, that worked perfectly and your instructions were detailed
enough for me. Now that I know how to accomplish this task, I will be using
it in a multitude of ways. Thank you again for your response and the trouble
to which you went. You have helped me enormousely.

Wendy Lauerman

George Nicholson said:
1) (optional) Make a backup of either your entire db or just the table
2) In Design view of the table, change the field type from
Number/LongInteger to Number/Single
3) Create a new query in Design view.
-Add your table to it.
-Add [TheFieldIWantToModify] to the QueryByExample grid.
-Change the QueryType to "Update Query" (there is a drop-down control in
the toolbar).
-In "Update To" under [TheFieldIWantToModify],
enter "[TheFieldIWantToModify] * .01"
(WITHOUT the quotes but WITH the brackets)
4) Run the query (the big Red !)

Hope this helps,
--
George Nicholson

Remove 'Junk' from return address.


Wendy L said:
What statement do I need to use to divide by 100? And where does that
statement need to be placed in Design View of the table? In the Format part?
Click to expand...
Click to expand...
 
Back
Top