Convert Text into Numbers in Microsoft Access 1

[Cherish2007]

Hello, in one of my tables I have a column with the Fund Code Name field and it lists Fund Codes as text, but i entered numbers, ie. 1, 2, 3, etc. Now i need to convert these numbers into 3 digit numbers. For example 1 should become 001, 2 should become 002. One condition should be present, these numbers have to be entered as text. Should i run a separate query to turn this text into 001 and 002 numbers and then input it into the main table? Please help, many thanks!

 

[PHV]

Have a look at the Format function. ("000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cherish2007]

Yes, i tried that, but then if i have a number 1000, i want to keep it as 1000, and if i use format function "000" it adds 000 to 1000 as well. I only want to do it till 99, i.e. 099 and stop there and leave 1000, 1001 as they are. Can it be done? Many thanks!

 

[PHV]

Any chance you could say us what you really tried ?
FYI, Format(1000, "000") gives 1000 ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cherish2007]

Ok, i have a table with Fund Codes: 1, 2, 3 all the way to 9999 (the data type is text). However i need to change Fund Codes to 001, 002, etc without changing the data type to numbers. Maybe i can try to use IIF function? I've never used IIF before, but it's my guess. If so, can you please tell me the right way to use it. Thank you!

 

[PHV]

Again, use the Format function.

 

[Cherish2007]

I do and it helps...1 becomes 001, etc, but 10, also becomes 1000 for some reason! and i don't want that. any chance i can avoid it?

 

[PHV]

for some reason
You have to discover why.

 

[Cherish2007]

How??? I have to do it for work as soon as possible, so i am really counting on your help. I am not sure why 10 becomes 1000. In Format should i use @00@? Thanks

 

[PHV]

Again, Any chance you could say us what you really tried ?

 

[Cherish2007]

I tried Format, but it didn't work. Any other suggestions please?

 

[PHV]

I tried Format
How ? Where ? What happens ?

 

[Cherish2007]

In a Table I use Format function and enter @00@. 1 becomes 001, 2 becomes 002 and 10 becomes 1000 and 11 becomes 1001. And all i need is for 1 to become 001 and for 10 to become 010 and for 100 to stay 100. Any suggestions? Thank you.

 

[PHV]

And what about what I've suggested: 000 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cherish2007]

If I enter 000 in Format, 1 moves to the right of the column and so are the other numbers. It doesn't become 001.

 

[PRPhx]

You could write code that tests for the length of your value. if the length= 1 then append "00" to the front of it (i.e.1 becomes 001) ELSE if the length = 2, append "0" to the front of (i.e.10 becomes 010).

In the future, you could do some data validation and automatically assign the correct number of 0's before your data is written to your db.

Or in your table design, set up an input mask that forces the user to add the 1 or 2 0's, as needed.

 

[Cherish2007]

PRPHx, thank you so much. I've never written a code before, would you mind helping me out? Should i do in a table or in a query? Thank you!

 

[Hap007]

Hi Cherish,

Code would be the best solution, but if you need it done now and you have never written code, then try this.

Open the table in design mode
Make sure the 'Properties' window is visible
Then, for the format value, type 4 zeros 0000
(The format of four zeros will give you 1000, 0200, 0325)

or

Then, for the format value, type #000
(This format will give you 1000, 200, 325, 010)

Save the table, open it and see if that solves your problem.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Cherish2007]

Hap007, thank you but when i do that nothing happens, because the Data Type of this field is Text not Numbers.

 

[PHV]

A one shot update query (SQL code example):
UPDATE yourTable SET [Fund Code] = Format(Val([Fund Code]), "000")
WHERE IsNumeric([Fund Code])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886