how can i format a number field to display max 5 digits?

  • Thread starter Thread starter THD69
  • Start date Start date
T

THD69

i would like to display only 5 digits of a number field. left digits should
display '0' if the number is less than 5 digits. like - 00325 if the number
is 325. Pls help...
 
First, can you confirm that the value in that field is of datatype
"numeric"? Or is the field a "text" field, showing digit characters?

If you want to change how a value is displayed, you can use formatting.

However, if the value you are referring to happens to be something like a US
Zipcode, remember that those are CODES, not numbers. Store them as text to
preserve the leading 0's. If you store them as "numeric", remember that
"00325" = "325" when they are numbers.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If the number is greater than 5 digits what do you want to do?

Right("00000" & [NumberField],5)

That will generate a string like the following (Actual value : String Result)
1 : 00001
535: 00535
98765123 : 65123
null : 00000

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thnx Jeff, it works...

Jeff Boyce said:
First, can you confirm that the value in that field is of datatype
"numeric"? Or is the field a "text" field, showing digit characters?

If you want to change how a value is displayed, you can use formatting.

However, if the value you are referring to happens to be something like a US
Zipcode, remember that those are CODES, not numbers. Store them as text to
preserve the leading 0's. If you store them as "numeric", remember that
"00325" = "325" when they are numbers.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Thnz a lot John. is it to use it in access or excel? should i right the
function in format of a numeric field or what?

John Spencer said:
If the number is greater than 5 digits what do you want to do?

Right("00000" & [NumberField],5)

That will generate a string like the following (Actual value : String Result)
1 : 00001
535: 00535
98765123 : 65123
null : 00000

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
i would like to display only 5 digits of a number field. left digits should
display '0' if the number is less than 5 digits. like - 00325 if the number
is 325. Pls help...
Click to expand...
Click to expand...
 
You can use that expression as a calculated field in a query. Or you can use
it as the control source value (with an equal sign at the start) on a form or
in a report.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thnz a lot John. is it to use it in access or excel? should i right the
function in format of a numeric field or what?

John Spencer said:
If the number is greater than 5 digits what do you want to do?

Right("00000" & [NumberField],5)

That will generate a string like the following (Actual value : String Result)
1 : 00001
535: 00535
98765123 : 65123
null : 00000

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
i would like to display only 5 digits of a number field. left digits should
display '0' if the number is less than 5 digits. like - 00325 if the number
is 325. Pls help...
Click to expand...
Click to expand...
Click to expand...
 
Thanz

John Spencer said:
You can use that expression as a calculated field in a query. Or you can use
it as the control source value (with an equal sign at the start) on a form or
in a report.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thnz a lot John. is it to use it in access or excel? should i right the
function in format of a numeric field or what?

John Spencer said:
If the number is greater than 5 digits what do you want to do?

Right("00000" & [NumberField],5)

That will generate a string like the following (Actual value : String Result)
1 : 00001
535: 00535
98765123 : 65123
null : 00000

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

THD69 wrote:
i would like to display only 5 digits of a number field. left digits should
display '0' if the number is less than 5 digits. like - 00325 if the number
is 325. Pls help...
Click to expand...
Click to expand...
Click to expand...
 
Would you pls tell me how can i calculate the Reducing balance depreciation
method in MsAccess pls?
 
New Question: Start a new thread.

I have no idea. And if you want someone to help you are going to have to give
them more information on your table and field structure. AND possibly explain
what you mean by reducing balance depreciation and how you would calculate it.

There is probably someone with enough accounting knowledge and knowledge of
how queries work to help you. That is not me.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
how can i formulate the following calculation in a query. or should i need
write a program? pls help...i need to calculate A, B and 4.88

cost rate A=(cost*rate) B= cost - A
10 0.2 2 8
8 0.2 1.6 6.4
6.4 0.2 1.28 5.12
 
how can i formulate the following calculation in a query. or should i need
write a program? pls help...i need to calculate A, B and 4.88

cost rate A=(cost*rate) B= cost - A
10 0.2 2 8
8 0.2 1.6 6.4
6.4 0.2 1.28 5.12
Click to expand...

All of the records in a Query must have the same "shape" (same number of
fields, of the same datatypes) - so you can't have three rows of one shape and
a grand total consisting of a single number *in a Query*.

What you can do is use a Query with fields [Cost], [Rate], A:[Cost]*[Rate],
and B: [Cost]*(1-[Rate]) - simplifying the calculation of B a bit, the
result is identical.

You can then create a Report based on the query, make the report Footer
visible, and put a textbox in the footer with a control source

=Sum([A])
 
Back
Top