# Changing Formatting of numbers (1 Viewer)

#### ConnorGiles

##### Strange Traveller
Hey guys,

Little problem, Is there a way to change the formatting of a numbers column to the formatting :

1 = 0001

11 = 0011

111 = 0111

1111 = 1111

But that would have to apply to this also

1 - 2 = 0001 - 0002

11 - 22 = 0011 - 0022

111 - 222 = 0111 - 0222

1111 - 2222 = 1111 - 2222

If that makes sense

feel free to ask if it doesn't

#### CJ_London

##### Super Moderator
Staff member
To format a number as 0001 set the control format to 0000

not clear if 0001 - 0002 is a text string, a calculation or the context it is used

but to build as a text string would be

format(1,"0000") & " - " & format(2,"0000")

#### ConnorGiles

##### Strange Traveller
It is plot numbers CJ.

Please understand that those wouldnt be the only 2 numbers i would use, there are thousands i could choose from.

So basically 1 - 2 cant be used in a number? because it would be a text string.

but 0000 would work in terms of being a number?

#### Minty

##### AWF VIP
I think the question is - Do you need them to be numbers or simply text?
If numbers are required then you can display them as you want (As CJ has shown), but still treat them as numbers in your application?

#### ConnorGiles

##### Strange Traveller
Thanks for the replies

I do not need them as numbers, (main reason for the numbers is for the order they come out in from a query) I have previously used text for the plot numbers but I was wondering if there was a way using the Number format to be able to incorporate the 1 - 2 rule .

This could involve 45 - 82 . Plot numbers are given to us via the order. These Plot numbers have never gone past 9999 since you cannot fit that many plots onto one site.

So can the 45 - 82 (0045 - 0082) rule be incorporated in any way?

#### ConnorGiles

##### Strange Traveller
Another problem - some records for plot numbers are laid out this way 1/2

or 45/89

and so forth

I have revently used a simply append query to change this before so that every / found is changed to " - " to resume the original formatting

but i want to know if i can use formatting to accept this rule also?

#### Minty

##### AWF VIP
Leave them as numbers - much easier to control the input and formatting.

I think you need to do something like this to get your data display correct
in a query against your orders you have StartPlotNo , EndPlotNo
Your output would be a calculated field ;
Code:
``DisplayRes: format(StartPlotNo,"0000") & " - " & format(EndPlotNo,"0000")``

Which would give you three columns
Code:
``````[FONT="Fixedsys"]StartPlotNo     EndPlotNo   DisplayRes
1                      66             0001 - 0066
66                    102            0066 - 0102[/FONT]``````

You can get cleverer with it to remove the second number if it's a single plot , but we can cross that bridge if you are unsure of how to do it.

#### Minty

##### AWF VIP
Double post!

I think you need to tell a little more here - are you looking at a way to capture these orders and store them? if so how do you get them? Can we get the whole story please?

#### ConnorGiles

##### Strange Traveller
Leave them as numbers - much easier to control the input and formatting.

I think you need to do something like this to get your data display correct
in a query against your orders you have StartPlotNo , EndPlotNo
Your output would be a calculated field ;
Code:
``DisplayRes: format(StartPlotNo,"0000") & " - " & format(EndPlotNo,"0000")``

Which would give you three columns
Code:
``````[FONT="Fixedsys"]StartPlotNo     EndPlotNo   DisplayRes
1                      66             0001 - 0066
66                    102            0066 - 0102[/FONT]``````

You can get cleverer with it to remove the second number if it's a single plot , but we can cross that bridge if you are unsure of how to do it.

Problem here mate is, I already have a query which does this

The problem I'm having is I need to change the formatting to be able to accept 1-2 or 1/2 and change them to 0001 - 0002

#### ConnorGiles

##### Strange Traveller
Double post!

I think you need to tell a little more here - are you looking at a way to capture these orders and store them? if so how do you get them? Can we get the whole story please?

Double Post Also!

No not to store, I just want to change the formatting. (Read above)

#### gemma-the-husky

##### Super Moderator
Staff member
seriously.

if you are talking about plot numbers on a building site, then a plot "number" is not a number. It's text. Sometimes you will have suffixes. eg plot 1A, 1B etc. You also do not need to do any arithmetic with plot numbers. Because it's text. This is the same treatment as phone numbers - They are text as well.

I would 100% treat your plot "numbers" as text fields.

The method you choose for handling a group of plot numbers, is going to be similar to, say, the way you select pages for printing on a report.

eg 1-4, 6

This is nothing to do with whether the numbers are stored as numbers or text, more to do with presentation, and data manipulation.

I am surprised you need the prefix zeroes, but if you treat the numbers as text, then plot 0001 is just that. Plot "0001" and the leading zeroes are just text characters. One benefit of storing the leading zeroes is that "natural" sorting becomes consistent because of the fixed number of characters.

you get 0001,0002, 0002A, 0002B, ..., 0010, ..., 0100

whereas if you sort without the leading zeroes you get a different "natural" sort order

1,10,100, 11, 2, 2A, 2B etc

Last edited:

#### ConnorGiles

##### Strange Traveller
seriously.

if you are talking about plot numbers on a building site, then a plot "number" is not a number. It's text. Sometimes you will have suffixes. eg plot 1A, 1B etc. You also do not need to do any arithmetic with plot numbers. Because it's text. This is the same treatment as phone numbers - They are text as well.

I would 100% treat your plot "numbers" as text fields.

The method you choose for handling a group of plot numbers, is going to be similar to, say, the way you select pages for printing on a report.

eg 1-4, 6

This is nothing to do with whether the numbers are stored as numbers or text, more to do with presentation, and data manipulation.

I am surprised you need the prefix zeroes, but if you treat the numbers as text, then plot 0001 is just that. Plot "0001" and the leading zeroes are just text characters. One benefit of storing the leading zeroes is that "natural" sorting becomes consistent because of the fixed number of characters.

you get 0001,0002, 0002A, 0002B, ..., 0010, ..., 0100

whereas if you sort without the leading zeroes you get a different "natural" sort order

1,10,100, 11, 2, 2A, 2B etc

Hit the nail on the head, but not entirely.

Yes some plot numbers consist of Letters instead of numbers but that wasn't the question.

So in text format, would there be a way to format it to automatically add zero's in front of numbers to assume natural order.

(I did say the order was the reason I was doing the 0000 before)

I cant seem to be able to find any other way rather than finding every number 1 and changing it to 0001 , then 2 and changing them to 0002 (bear in mind this is using an append query) but this has taken me days before since I have over 2000 different plot numbers not including ones like 1 - 2 which are even more of a burden.

Was just wondering if there was a quicker way for in the future when our final excel spreadsheet is transferred to Access.

#### CJ_London

##### Super Moderator
Staff member
you would need to use the split function to convert 1/2 into two separate numbers then format each number then recombine

something like

Code:
``````public function reformat(s as string) as string
dim sarr() as string
dim i as integer

sarr=split(s,"/")
for i=0 to ubound(sarr)-1
reformat=format(sarr(i),"0000") & "/"
next i
reformat=left(reformat,len(reformat)-1)

End``````

You would need to modify if you have a variety of separators ('-',' - ',' to ', etc.)

But really I would go back to your website designers and get them to tighten up what is allowed to entered - i.e. have to input boxes ad only allow numeric input - at the moment you are allowing users to input 'rubbish' and then you are having to clean up the mess

#### ConnorGiles

##### Strange Traveller
The split function isn't needed CJ unfortunately.

As I've said a simple Append Query can change every / into - which would make the format the same for those types.

But that isn't the problem

Problem is changing 1 to 0001 when it is included within something like this 1 - 2

same with 2 ( and so on )

#### CJ_London

##### Super Moderator
Staff member
So in text format, would there be a way to format it to automatically add zero's in front of numbers to assume natural order.
Hadn't appreciated this was only for ordering purposes

If that is the case then in your query (assuming plot number is text)

ORDER BY val(plotNumber)

Val will convert

'0011' to 1
'3-15' to 3
'45/55' to 45

etc

#### ConnorGiles

##### Strange Traveller
But really I would go back to your website designers and get them to tighten up what is allowed to entered - i.e. have to input boxes ad only allow numeric input - at the moment you are allowing users to input 'rubbish' and then you are having to clean up the mess

Its not the website designers CJ, They send them through fax and email to our production manager and from there it is entered into the database manually.

But yes that would make this job so much easier

#### ConnorGiles

##### Strange Traveller
Hadn't appreciated this was only for ordering purposes

If that is the case then in your query (assuming plot number is text)

ORDER BY val(plotNumber)

Val will convert

'0011' to 1
'3-15' to 3
'45/55' to 45

etc

Plot number is indeed text,

I already have a query which works

It asks for the first plot number and the last plot number, and if the first number is 0011 and the last number is 0016, If there is a text that says "0011 - 0016" that would show up as well as numbers such as 0011, 0012, 0013, 0014, 0015, 0016.

I don't think I am explaining properly,

I have already made a database in which has this column as a text format and I changed every number from 1 to 3000 into the 0001 format through an append query and the numbers such as 1 - 2 had to be manually changed through the find and replace function.

We are going to update the database with a new table which needs the plot numbers changing again. (bear in mind this took days before) - I wish to find a quicker way than the previous way.

I was just wondering if there was a way to achieve this without doing my old method.

Sorry if you all misunderstood me in the first place. I'm not too good at explaining my situation very well

#### CJ_London

##### Super Moderator
Staff member
OK so you want to search data like this

0001
0022-0033
0011-0022

and find all the records which contain the searched for number. so looking for 0022 will return

0022-0033
0011-0022

for 0001 will return

0001

and searching for 0015 will return

0011-0022

Is this correct?

And, since data is added manually why do you not have two columns (plotFrom and plotTo) which would make any search much easier?

#### CJ_London

##### Super Moderator
Staff member
They send them through fax and email to our production manager and from there it is entered into the database manually.
So have the input form they use modified instead

#### ConnorGiles

##### Strange Traveller
OK so you want to search data like this

0001
0022-0033
0011-0022

and find all the records which contain the searched for number. so looking for 0022 will return

0022-0033
0011-0022

for 0001 will return

0001

and searching for 0015 will return

0011-0022

Is this correct?

You were on the right tracks until you said 0015 will return 0011-0022 ( that is wrong because plots like 0011-0022 are input like this because that is an apartment block my friend. so it is not separate plots.

And, since data is added manually why do you not have two columns (plotFrom and plotTo) which would make any search much easier?

Because we need plots like 0011-0022 to establish that it was an apartment block. Searching isn't the problem CJ - I've already completed that. The problem is that I want to find a way to format 1 - 2 into 0001 - 0002 quickly and with every number this format entails.

Replies
14
Views
215
Replies
37
Views
684
Replies
5
Views
263
Replies
11
Views
406
Replies
5
Views
114