decimal places

[toddbingham]

Importing data, need to force a decimal point to the left of the right 2 numbers. Should this be done on the link table or in the query?

Thanks.

 

[llkhoutx]

You can't adjsut the decimal when linking. Linking just tells the program which folder and file the data is in.

Generally, absent some compelling reason, adjust the decimal places in your data if and when it's displayed.

 

[toddbingham]

Thanks.

 

[toddbingham]

OK, have a problem. I have the feild on the link table set to currency, an append query appends the fields to a table, which I have a field that is formatted to currency. But when the feild is appended, it adds 2 zeros to the end of the number, for example:
120000 should be converted to 1200.00 but it is being formatted as 120000.00

I dont need it like that, I need it to format it like 1200.00

 

[toddbingham]

Does any one have any idea on how to force the decimal place 2 spots to the left? I can put the figure I want on a report be dividing by 100 when I pull it onto the report. But, hen there is a zero as the last number, it leaves it off making it look like 557.6 instead of 557.60

I want it to look like the latter.

 

[KKilfoil]

Divide the number by 100 as you are doing, and then FORMAT the control to display two decimal places

 

[toddbingham]

Tried that. I set up an Input Mask named Currency for that purpose, that doesnt even work. Here is a stripped down version. Here you can see what is happening. Let me know what I should do.
Example:
The first person on the table Amy Abbott has a ACREDIT of $59,365.00 it should be $593.65

 

[KKilfoil]

Setting an input mask doesn't make much difference if you are importing.

The data is arriving in pennies rather than dollars. You have three choices:

1) Create an update query and divide the values in the table by 100 EACH TIME you import. This is clumsy and you run the risk of accidentally doing the division twice

2) Continue on as you are doing on your report(s) and form(s) by calculating the /100 value each time you want to display it. Then change the FORMAT of the appropriate control in design mode to be eitther Fixed and 2 decimal places, or Currency and 2 decimal places (You have shown both outcomes in your samples!)

3) [My Recommendation] Create a QUERY and calculate the /100 values ONCE (plus whatever other fields you need later), and then use this query wherever you are presently using your table. You still have to manage the format of the various controls.

HTH

 

[toddbingham]

Ok, that seemed to solve the problem. I ran an update query dividing by 100 and set it to a fixed number with 2 decimal places. Now the problem I am running into, is when I print it on my report, it still doesnt print the last zero if the last cent is a zero. For example 557.60 is coming out like 557.6
How can I make it not drop the last zero?

Thanks for all of your help.

 

[KKilfoil]

Did you try this ...?

Then change the FORMAT of the appropriate control in design mode to be eitther Fixed and 2 decimal places, or Currency and 2 decimal places (You have shown both outcomes in your samples!)

Sometimes changing the format at the table level does not fix up forms and reports. You can always force or override a format at the individual control by changing its design properties.

 

[toddbingham]

What do you mean? Adding an input mask in the design of the field at the report level?

 

[KKilfoil]

Open the report in design mode.

Select the text box containing the field you are trying to change

Right-click

select properties from the popup menu

look on the Format tab for the 'Format' property. Select 'Currency'

look on the Format tab for the 'Decimal Places' property. If necessary, change 'Auto' to 2.