Changing Formatting of numbers

[ConnorGiles]

So have the input form they use modified instead

No CJ,

None of this is a problem

Read to my previous post. I want a quicker way to make 1 - 2 into 0001 - 0002 and so forth.

 

[BlueIshDan]

Got it
Add this:

000000;-000000;"Nil";""

to your format property of your numeric field.

If that's even what you're trying to do! lol

 

[ConnorGiles]

Its a Text field Dan Lol

Sorry!

 

[CJ_London]

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.

The solution to this was posted way back in post#13

 

[Minty]

No CJ,

None of this is a problem

Read to my previous post. I want a quicker way to make 1 - 2 into 0001 - 0002 and so forth.

Actually THIS is your problem...

If you forced the input to the database into the correct format at the point of data ENTRY you wouldn't have all the messing around afterwards. Fix the problem at source not afterwards.

 

[ConnorGiles]

Actually THIS is your problem...

If you forced the input to the database into the correct format at the point of data ENTRY you wouldn't have all the messing around afterwards. Fix the problem at source not afterwards.

Wasn't me who did the input

I am actually here to convert over from excel

So I will pass this "Helpful" advice over to the people who input into the excel spreadsheet.

 

[ConnorGiles]

The solution to this was posted way back in post#13

Ill try it CJ - Thanks.

 

[gemma-the-husky]

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.

I know the thread has moved on a bit - but a number is a number.
You cannot change a number by adding leading or trailing zeroes.

so 3 and 003 and 0003 are all just the number 3. Getting leading zeroes displayed in front of a number is simply a matter of formatting, and nothing to do with the actual number.

text is different. lexicographically (if that is the right word) "3", "03", "003" and "0003" are all different text values.

if you enter a text value "12", it is trivial to amend this to be "0012". given an entry field myplot

myplot = right("0000" & myplot,4) returns the correctly formatted 4-char string.

so "12" becomes "0012"
"12A" becomes "012A". If you need this to be "0012A" then it needs a bit more work.

I would point out that having letter suffixes is more than just an observation. A number just cannot have a letter suffix. If you do need to deal with suffixes, then a numeric field isn't an option at all.


are you saying you have a plot number "1 - 2" ? How can that possibly be?

 

[gemma-the-husky]

What I would do is this.

just as an observation, I do not actually think you want the numbers to be dealt with as four figure strings.

I think your plots should be text values, but stored as natural numbers,

Therefore you have plots "numbered" (but as text)

1
2
3
3A
3B
3C
...
...
10
11
12
...
20
etc


now the problem is that for sorting purposes these do not sort correctly. So when it comes to sorting, you need a column in your query

sortorder: val(plotnumber)

now the numeric values of the plots are returned correctly, and you get a natural sort order.

And now you also do not have the problem that if someone enters a search for plot 2, you have to manipulate this into 0002. you just search for "2"

hope this helps.

 

[ConnorGiles]

are you saying you have a plot number "1 - 2" ? How can that possibly be?

Because that would be a apartment block my friend - or flats.

Plot numbers such as 1-2 or 43-84 would be flats or apartments so it would take up more than one plot number.

As for the rest - I am having trouble getting it to work. I understand the way you are saying the 0000 formatting isn't needed - but I have tried to amend the new column sortorder: val(Plot No) <---- >(Thats the name of my column.)

That doesn't seem to work ? - It only shows up incorrect syntax or does nothing to the query at all .

I'm also having trouble amending the code CJ has given to me - because it doesn't seem to be working either - I tried using a VBA module and it did nothing either.

Any advice?

(Please take note, I cannot link my database as it is a terribly large file size even after using the repair)

 

[gemma-the-husky]

I struggle to see why a single "unit" should consist of multiple "plots", but that is besides the point.

If you have a unit that you want to designate as plot "1-2", then there is no problem, if the field is stored as text.

So what is your ACTUAL problem? Do you only have a numeric input field for the plots, even though the plots are not all numeric?

 

[ConnorGiles]

I seem to think you are over-thinking it Dave!

Each Plot is for a single (or multiple) people - such as 1 plot = 1 house

things such as 1-5 would be an apartment complex or flats because there are houses for 5 people/couples (or whatever) within.

Basically each plot consists of 1 house, multiple plots consist of more than one (which would be apartments or a flat).

Order is the problem Dave. (I have said this numerous times) the numbers are stored as text - but ordering 1-5 and 81-102 is a difficult thing.

I wish to know an easy but effective way to order numbers such as 1-5.

THAT is the problem.

 

[CJ_London]

since you have a space in your field name it should be

val([Plot No])

 

[ConnorGiles]

since you have a space in your field name it should be

val([Plot No])

Where should this be placed? Because I have tried placing this in a VBA module but it doesn't seem to be working.

Any tips?

 

[gemma-the-husky]

I seem to think you are over-thinking it Dave!

Each Plot is for a single (or multiple) people - such as 1 plot = 1 house

things such as 1-5 would be an apartment complex or flats because there are houses for 5 people/couples (or whatever) within.

Basically each plot consists of 1 house, multiple plots consist of more than one (which would be apartments or a flat).

Order is the problem Dave. (I have said this numerous times) the numbers are stored as text - but ordering 1-5 and 81-102 is a difficult thing.

I wish to know an easy but effective way to order numbers such as 1-5.

THAT is the problem.

I think you are not being totally clear. What do you mean order numbers 1-5? Do you want an entry for plots "1-5" to result in a list of plots as follows?

1
2
3
4
5

if so then look at split function

split "1-5","-" gives an array 1 with elements 1,5
these values can then be used to select plots between (and including) 1 and 5 - just a standard query.

 

[ConnorGiles]

No what I mean is -

the record "1-5" to be reformatted into 0001-0005 without splitting it or taking it out of text format.

This needs to stay together as it needs to signify a apartment complex or a block of flats.

I know you've said we don't need to do the 0000 formatting, but I do not know how to achieve the correct ordering without it!

Also another problem for this is that I need to be able to make sure numbers like "1A" turn out like 0001A not 001A.

I'm starting to think that I will have to do it the way I did in the first place Find and replace or a Update Query

 

[gemma-the-husky]

this function any good "reformat". I imagine the "y" string slicing bit could be improved a bit.

Function reformat(s As String, splitchar As String, length As Long) As String
's is the plot string
'splitchar is the char used to separate the plots
'length is the required length
  
 Dim a() As String
Dim x As Long
Dim y As Long
Dim result As String

 a = Split(s, splitchar)  'turn the string into an array
 For x = 0 To UBound(a)
    If IsNumeric(a(x)) Then
        a(x) = Right("0000" & a(x), length)  'just process a numeric plot
    Else
        y = 1
        While IsNumeric(Mid(a(x), (y), 1))  'find the first non numeric char
            y = y + 1
        Wend
        a(x) = Right("0000" & Left(a(x), y - 1), length) & Mid(a(x), y) 'process a non-numeric plot
    End If
Next

 'now reassemble the string
 result = ""
For x = 0 To UBound(a)
    If x > 0 Then
        result = result & splitchar & a(x)
    Else
        result = a(x)
    End If
Next
reformat = result
 
End Function
  
 Sub test()
MsgBox (reformat("1-2A", "-", 4))
End Sub

 

[ConnorGiles]

the record "1-5" to be reformatted into 0001-0005 without splitting it or taking it out of text format.

I do not wish for it to be split - or is that necessary for it to work? and I'm quite a novice at VBA.

So I know it needs to be a module, But how would I implement it into the database?

 

[CJ_London]

Connor - everyone is giving you the same advice.

Why don't you just try my suggestion in post #13 r Gemma's above, thoy both achieve the same thing - converting

1 - 2

to

0001 - 0002

 

[ConnorGiles]

I did ask CJ - How I would implement that in post #13 - due to the fact I have tried with no prevail

But no replies

I also tried Dave's but yet again no success...