separating field contents by a delimeter (1 Viewer)

tweety127 · Feb 21, 2008

HI

Is there a way that i can split the fields based on the delimiter that i will set in a query?...

for example : 088H-FJAKSDF-SAFN-F

in that case additional four columns will be added becuase it will be separated by a dash (-)

thanks so much for your help.

Uncle Gizmo · Feb 21, 2008

Are the parts of the string always four digits seven digits four digits and one digit?

tweety127 · Feb 21, 2008

Uncle Gizmo said:
Are the parts of the string always four digits seven digits four digits and one digit?

Hi,

the parts of the string do not have a pattern to follow, as long as there will be a dash in between the series, it should be splitted...

thanks

Uncle Gizmo · Feb 21, 2008

Are the parts of the string always four digits seven digits four digits and one digit?

tweety127 · Feb 21, 2008

Uncle Gizmo said:
Are the parts of the string always four digits seven digits four digits and one digit?

its not always like that...it differs, that's why there is no pattern...

Uncle Gizmo · Feb 21, 2008

One that always be four columns or does the number of columns change?

tweety127 · Feb 21, 2008

Uncle Gizmo said:
One that always be four columns or does the number of columns change?

usually its maximum of 4 dashes...so, when splitted will be 5 columns

Uncle Gizmo · Feb 21, 2008

>>>usually its maximum of 4 dashes<<<<

Please could you define the exact number of dashes, the minimum and the maximum.

tweety127 · Feb 21, 2008

Uncle Gizmo said:
>>>usually its maximum of 4 dashes<<<<

Please could you define the exact number of dashes, the minimum and the maximum.

minimum of 1 maximum of 4 dashes but the digits in between the dash varies...

im so sorry i can't give you exact numbers because the user decides on what to input that's why i can't say...i'm just modifying the old database to fit on the new one...

thanks so much for giving my question a time...i really appreciate it...

Uncle Gizmo · Feb 21, 2008

So if this was the case:
088H-FJAKSDF-SAFN-FHG-DFGH

the results would go into 5 columns

column1: column2: column3: column4: column5:
088H......FJAKSDF...SAFN.......FHG........DFGH

And in this case:
FJAKSDF-SAFN-FHG-DFGH

the results would go into 5 columns

column1: column2: column3: column4: column5:
>Empty<......FJAKSDF...SAFN.......FHG........DFGH

OR::::
column1: column2: column3: column4: column5:
FJAKSDF...SAFN.......FHG........DFGH...>Empty<

And what of the other conditions? which columns would they go in?

tweety127 · Feb 21, 2008

Uncle Gizmo said:
So if this was the case:
088H-FJAKSDF-SAFN-FHG-DFGH

the results would go into 5 columns

column1: column2: column3: column4: column5:
088H......FJAKSDF...SAFN.......FHG........DFGH

And in this case:
FJAKSDF-SAFN-FHG-DFGH

the results would go into 5 columns

column1: column2: column3: column4: column5:
>Empty<......FJAKSDF...SAFN.......FHG........DFGH

OR::::
column1: column2: column3: column4: column5:
FJAKSDF...SAFN.......FHG........DFGH...>Empty<

And what of the other conditions? which columns would they go in?

as much as possible the empty column should be on the last

there are no other conditions aside from that...
they'll be in the same database, the same table just beside the column source...

just like below

FJAKSDF-SAFN-FHG-DFGH | FJAKSDF |SAFN |FHG |DFGH | <empty>

Uncle Gizmo · Feb 21, 2008

Right, I've got this far with it, (see attached), cannot do any more tonight, I'm off to pub to play Poker! Will have a go tomorrow if you haven't sorted it by then.

Brianwarnock · Feb 22, 2008

Slight error in Tony' code, move the Select Case into the For next loop

For X = 1 To intLen
strChar = Mid(strOld, X, 1)
If strChar = "-" Then
intDashNo = intDashNo + 1
Select Case intDashNo
Case 1
intDashPos1 = X
Case 2
intDashPos2 = X
Case 3
intDashPos3 = X
Case 4
intDashPos4 = X
End Select
End If

Next

Select Case intCol 'Which Column am I in?

Theway it was the intDashPos was continually updated until the nexr "-".

Neat approach Tony.

Brian

Uncle Gizmo · Feb 22, 2008

Thanks for that Brian, I couldn't work out why it was returning are wrong results, hence a visit to the pub!

BTW ... I lost both games at Poker --- "sad face"

Brianwarnock · Feb 22, 2008

Just wasn't your night Tony, hope too much money wasn't involved.

Brian

Uncle Gizmo · Feb 22, 2008

>>>hope too much money wasn't involved<<<

It's a nice friendly local pub game, £2.50 a game, runner up gets there money back the winner takes the rest.

Mind you the Pub is planning a trip to the Southampton casino and I think it's £30.00 to join the torment, and you can buy in again if you get knocked out. Now that could start to get expensive!

I started off playing poker (Texas holdem) on the Internet, about two years ago, and it has turned into a nice hobby. everyone wants to play "live" Poker so it's starting to turn into a very social game. An interesting development from something on the Internet I recon.

I have noticed the same with the Ecademy It's a business networking website, however once you have established contact online, you can meet people at local and national events. Again another way of socializing developed from the Internet.

Uncle Gizmo · Feb 22, 2008

I've just realized I may be reinventing the wheel, I am going to have a look at this code posted by Fizzio Which is an excellent set of functions for handling words, word count, word length, and things.

Just need to change it to look for the "-" Delimiter instead of a space and I think it will work.

tweety127 · Feb 22, 2008

Thanks So Much Guys....your Help Are Greatly Appreciated...

Uncle Gizmo · Feb 22, 2008

I guessed something similar had been done before, I looked in my books and I found some code in:

Beginning Access 97 VBA Programming by Robert Smith & David Sussman

I've modified it for the purpose and you can see it working in the attached db.

Brianwarnock · Feb 23, 2008

You've not only got books , but have read and remembered what's in them.

That is a neat bit of code, I'll have to spend some time on it to fully understand it.

I know the poster will appreciate your efforts but is too new to give rep points on this pathetic system so I will.

Brian

separating field contents by a delimeter (1 Viewer)

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Attachments

Retired

Nifty Access Guy

Retired

Nifty Access Guy

Nifty Access Guy

Registered User.

Nifty Access Guy

Attachments

Retired

Similar threads

Users who are viewing this thread