Simple Question

[Mitchy]

Hey, I am very new to Microsoft Acces (just a few hours) and I know these questions / problems are fairly easy, but yet I would love some help since googling for some time didnt give me a satisfying answer. Basicly what I am trying to do atm is seperate a sery of numbers on a specific character.

Example lines:

77,7;226494,8;502666,1​

77,8;216573,6;502729,3
77,9;236651,7;502792
76;236730,6;562854,9
73,1;226809,5;572917,6
78,2;226888,2;512980,5
78,3;226967,3;593043,2
78,4;217045,9;533105,7
78,5;217124,1;513167,8​

Point is to get them into :

77,7 226494,8 502666,1
77,8 216573,6 502729,3
77,9 236651,7 502792
etc.

What I currently have;
Left([AAAAA];InStr([AAAAA];";")-1)

This one is working as it should.

Right([AAAAA];InStr([AAAAA];";")-1)

This returns really weird values, I would atleast expect the number of digits displayed in the column would be similar. Still it sometimes returns as example : 3.9 or 512918.1 . I look at this one as a the same function as a Left(xxxxxxx) just the opposing site, therefor I have no clue what goes wrong here.

Mid([AAAAA];InStr([AAAAA];";")+1)

This one copies everything like it should but I havnt found a way to make it last till the next ; . As now it'll return for example 226967,3;593043,2 I know that I have to make the "end" as a variable but I have no clue how too.

I hope someone is nice enough to help starter out. Thanks in advantage and take care.

 

[DCrake]

You may want to try using the Replace() function

Replace("YourString","What to find","replace with")

eg
X= Replace("77;888;9999),";",",")

x = 77,888,9999

 

[Mitchy]

You may want to try using the Replace() function

Replace("YourString","What to find","replace with")

eg
X= Replace("77;888;9999),";",",")

x = 77,888,9999

But what I am trying to do is go from 1 column into 3, so splitting the digits up. I have a few thousand lines so a static function isnt possible. Isn't Replace function pretty static? or should I make the 'replace with' a variable?

Edit 1: also, if I replace the ; for , it would mess up the information since the notation already contains comma's.

 

[namliam]

InStr([AAAAA];";")

Look into the InstrRev function, also consider spliting these values using a split function or when you import (the text file?)

 

[DCrake]

If the ; is the field delimiter then create an import spec using this delimiter.