Related Topics

Make A Textbox Always Display 2 Decimal Digits.

Rounding to 2 decimal places
I am computing a value based on a %. If I round and the answer is say 100.125 the result is 100.12 instead of 100.13.

If I add 0.005 I will get the correct value for this, but if I get an answer of say 100.3848 and I round it after adding the .005, my answer is wrong.

My problem would be solved if I could test the number of digits after teh decimal place.

using the right(Cinput, 1) function to test the last decimal, it returns either the 2nd, 3rd or 4th digits depending on how many decimal places there are.


Stop text box from rounding decimals
I am using Access 2003 and have a form with a number of unbound text boxes formatted to Standard number, 2 decimal places.

The problem is that whenever you enter a number with more than 2 decimals the textbox rounds the second decimal place up or down which I don't want. I just want to drop off any decimals greater than 2.

For instance if I enter 12.225 in a textbox the value displayed is rounded up to 12.23 and I want it to display 12.22 and ignore the third decimal.

I tried an input mask and that works but is not ideal for my purpose


Number conversion
I need to convert a number that is currently formatted as Double to a 7-digit format without decimail points. Using a query, I can create the calculated field to get rid of the decimal point, but I need for the field to be 7 digits long, with leading 0s to make up all 7 digits.

For example, 153.15 should become 0015315. I can only get it as far as being 15315. Is there a way I can set Properties to format this field, or is there another expression I can add to my calculation? I'm multiplying the source field by 100 to get rid of the decimal.

Any help will be appreciated as I'm stuck on this step.


label and currency textbox
I have a label with a textbox directly underneath it. The are aligned to the right. The textbox contains a currency amount and yet they do not display aligned.

How can I make the last character of the label line up with the last decimal of a currency value?


Converting COBOL picture format number to regular decimal number using SQL
does anyone know what built in function I can use to conver a COBOL number into a regular decimal number? For example

COBOL Picture
9(09)v9(06)

so the number would be 000009059790000

and the conversion would be 6 digits to the right of decimal and 9 digits to the left. so the output I would want is 9059.79

I was doing the following but its displaying the 0's in front and I didnt want that.so I am getting 000009059.790000

LEFT(Income, 9) + '.' + RIGHT(Income


Limiting decimal places
I would like to limit the decimal places on a percentage. The working Expr is [ActiveHours]/[TotalHrsPaid]#100 What would I need to add to restrict it to display only 2 decimal points and to display the percentage sign


Rounded number inside of text
I have a query with a calculation that gives me around 10 digits after the decimal point. Then on a report I want to use that number but only rounded to 3 digits after the decimal point. I could change the format but there is also a twist. I want to put that number inside of text.
Basically I want a control box that has a control source of

="(TRIM GIRTH= " & [Trim Girth] & ")"

Is there a way for me to make it round it to the nearest three while still in the query?


Formatting currency with the cents but without the decimal point
I have a client who needs to export check information in a text format. The bank is requiring that all fields take x number of characters, which I have been able to do except that the check amount needs to read 10 digits without the decimal place. ie: $247.84 becomes 0000024784. I can get it to 00000247.84 with the function Format([CkAmount],00000000.00) but the minute I take out the decimal, I get a decimal, I get 0000000247.

I should also mention that this is an Access 97 db that has to be totally redone at some point in a later version. It won't convert to any higher a version because there is a lot of code in it that goes back to either Access2 or Access 95.


displaying the time 12:00:00 AM
I can make the time 12:00:00 AM display in a textbox by itsself but when I try to make it display with a date if will not show up in the textbox

it seems to be there because I can use datediff and my formula works but cant make it display


Numbers look right on the form, report shows #####
I've just spent a couple days getting a form to display the numbers on a subform with the correct number of decimal places. WHEW!

However, I'm trying to print a report from that form and subform. Of course, we're back to the same problem. I need to see a report AND a subreport that display the same calculations, and I seem to be right back where I started.

On my subreport, the numbers that appeared on the form are back to displaying ####### instead of a few digits with two decimal places.


Formatting Numbers in query results
I have a query that returns employee tax data I then export to a text file to upload. I am having trouble figuring out how to format my numeric amount fields to what is required. I need to have my numeric fields at 11 digits in length, with leading zeros and two decimal places with an implied decimal (no decimal in output). So if I have 123.4 - I need it to output from the query as 00000012340 - any ideas on how I can do this


Display actual values instead of #'s
Are there a way (in Access 2010), that the text boxes will not display #### for values that does not fit into the textbox?

I've got some reports where there can be quite large values in, and then the textboxes display ##### for these large values. I would rather have it to display what can, even if it means that the left most digits might not appear


Rounding of number not desired
I am trying to make two calculated fields come out looking the same in a query. When I hover the mouse cursor over them one has a value 48.9932 and the other has a value of 48.9974. When the mouse cursor is moved away they round off and display as currency as $48.99 and $49.00 respectively. In the calculated formula for each of them there are some 3 decimal place multipliers used. Could this be causing the problem. I must have the 3 digit multipliers in the formula. I have tried changing number types, decimal places, etc in the underlying table, but nothing seems to prevent them from displaying this way. In the calculated formula for each of them there are some 3 decimal place multipliers used. Is there a way to make them come out even by ignoring the 3rd and 4th decimal place


Negative Number Format for Decimal or Double
I'm using Access 2003.

In a form I have a textbox control that displays numbers in Standard format with three decimal places. I'd like to be able to display them with a negative number format (displaying parentheses rather than the negative sign): Unfortunately, I cannot seem to get it to display that way. I had thought that I could accomplish this by simply changing the Negative number format in my PC's regional settings (Control Panel), but that doesn't seem to work.

I have other textbox controls displaying numbers as Currency. If I set my PC's regional settings so that the Negative currency format is ($1.1), the controls display correctly in Access. However, with the controls that display numbers, not so. (They keep displaying them with negative sign rather than parentheses.)

Any idea where I'm going wrong?


Display only the last four digits of identification numbers
I know how to get excel to show only the last four digits of ID numbers. At first I thought I can just import the excel file with the last four digits and hide the column with the full ID numbers but all got imported to Access. If I am stuck with this, how do I get Access field to display only the last 4 digits? I do not want to show "***-**-****", just the last 4 digits only.

In excel I would use the RIGHT function, "=RIGHT(A1,4)." Can I do this type of function, "=RIGHT([field name],4)?


Format decimal numbers with fixed width
How can I format a decimal number so that:
. the width of the whole number is 10 characters
. 2 digits always after the decimal point
. for positive and negative numbers

Format(34.5, ###0.00) gives 34.50
This varies the number of spaces to the left of decimal point

Format(34.5, 0000.00) gives 0034.50
This gives a fixed width but not for negative numbers and it looks ugly

I want to print tables and have the columns line up
For example (I hope the numbers below do line up)
Number1 Number2


calculating Time from Decimal in access 2003
I have a problem calculating Time from decimal values. Could someone please help me.
Description:
(hr:min)I have a textbox on my form which takes input as "start time" e.g. 02:30. There is another textbox that takes in "Minutes" e.g. 90 as input and another textbox that takes in "extended time in minutes" e.g. 60 as input.

Now based on these three i.e. "start time" , " minutes" and "extended time in minutes" i.e. 02:30 + 90 min + 60 min I have to calculate and display "End time" in another text box .in this case it will be 05:00.

Can someone please tell me how do I calculate this at the click of a button


Fix Decimal Places on a Make Table query
I run a Make Table Query, and I would like the decimal point of Number columns to be set to 4 decimal points. I seem to have tried every combination possible but without success.


Textbox data validation rules.....
I'm really having issues with my validation logic rule tied to a textbox in a subform. Someone please help! My head hurts. LOL! Been looking at this stuff to long today. I need to force a numeric value in a text box that will allow NULL and decimal. I realize I could change the table field data type to decimal but that is not an option here.

Allow these characters in a text box:
1 2 3 4 5 6 7 8 9 0 . NULL

Heres what I have so far. This allows numbers but not decimals

Is Null Or Not Like "*[!0-9]*"


Decimal seperator between different languages
I am forming a Dynamic SQL using a decimal number in a different Regional language, which has comma as a decimal separator. How do I convert the decimal number to the format that can be used inside a query.