Problem with info

[paul123456]

I got about 50,000 records that i need to fix. the problem is is that is shows up like this..

12054NAME OF SOMETHING
545NAME OF SOMETHING
144NAME OF SOMETHING
001NAME OF SOMETHING

Theses are all like this but in order by number. what i need to do is seperate the number and letters into two different cells. how can i accomplish this? again there are 49,000 records so im hoping i don't have to do it one-by-one? maybe theres a query i can run or something? if you can help plz post.

thnx
Thanks, PAUL

 

[coco86]

Try this:

SELECT Left([fieldname],InStr(1,[fieldname],"name",1)-1) AS numberpart, Mid([fieldname],InStr(1,[fieldname],"name",1)) AS Textpart
FROM Table;

 

[coco86]

Oh, wait, that won't work will it? "Name" is just in your example, not your actual data.

 

[coco86]

What you want is a custom function like this

Public Function NumberPart(ByVal inputtext As String) As String
    Dim i As Integer
    Dim ThisCharacter As String
    
    i = 1
    ThisCharacter = Left(inputtext, 1)
    
    While IsNumeric(ThisCharacter)
        NumberPart = NumberPart + ThisCharacter
        i = i + 1
        ThisCharacter = Mid(inputtext, i, 1)
    Wend
    
    
End Function

and then in your query use

numberpart([fieldname]) as NumberField

You can write a similar function to get the remainder of the field or you could do

mid([fieldname],len(numberpart([fieldname])+1) as TextField

 

[paul123456]

talk about hardcore...right on..

questiong...do i make a query and in the field name put.

Public Function NumberPart(ByVal inputtext As String) As String
Dim i As Integer
Dim ThisCharacter As String

i = 1
ThisCharacter = Left(inputtext, 1)

While IsNumeric(ThisCharacter)
NumberPart = NumberPart + ThisCharacter
i = i + 1
ThisCharacter = Mid(inputtext, i, 1)
Wend
???????

were do i put this info? im scared. were do i put this info i dont understand what u mean by custom funtion

I have on table with with one field

table = test

field = info

I can't believe this is possible but i want to try it. Thanks, PAUL

 

[coco86]

Put the function in a module

In your query builder, use test as the table

create two fields

NumberField:numberpart([fieldname])

and

TextField:mid([fieldname],len(numberpart([fieldname])+1)

Then run the query.

If you've got 50,000 records, this is going to take awhile to run. I hope you've got a lot of RAM!

Just in case, make sure you've saved it all before you run it

 

[paul123456]

ok it worked good, the only thing i had to do is add a ) to the end of

TextField:mid([fieldname],len(numberpart([fieldname])+1)

did i do that correctly

the numbers did copy over to the number field but it did leave some numbers on the text field

for example if the number was

28516 it left the number 6 in front of the textfield
28517 it would have a 7
28518 it would have an 8.

it looks like a patern on all but on some it has this.

02515 would have 15 in frontof textfield
00231 would have 231 in front
00003 would have 00003

is there a function i can run that takes out all of the numberic numbers that are in the textfield? and did u come up with this in your head or did u research? i can't beleive it.
Thanks, PAUL

 

[paul123456]

Let me clarify a bit..it copied the exact number to the number field and did not loose any info and all of them are correct. the only thing is is that left copies of some numbers in the text field.

all of the numbers are correct in the number field Thanks, PAUL

 

[coco86]

actually, I think the missing ( goes

mid([fieldname],len(numberpart([fieldname]))+1)

Sorry, I tend to miss those

Let me work on the other problem a bit

As for how I got this idea, I did some research a long time ago and learned you could write your own functions. The rest is part research, part trial and error. I'm a Visual Basic programmer who does a lot of Access programming so I tend to solve most of my problems with code.

 

[paul123456]

sweeeeeeeeeeeeeeeeeeeeeeeeeeeet.... thnx a bunch works perfectly.hehehehehe Thanks, PAUL