parse field data (1 Viewer)

[PaulC999]

Unfortunately I have no choice but accept data into a memo field in the following format:- Make: Ford Model: Mondeo Colour: Blue

I need to make a query that will parse through and allow me to seperate the three categories and create three seperate fields. The data is always imported in the format of (Label: data)as shown above.

I have tried using the Right() Left() Mid() functions but because they cannot work because they truncate by a specified number of chars.

I would welcome any suggestions.

 

[PaulC999]

Further to the above:

If it makes a diference I could probably arrange to have the data supplied in a fixed lenth e.g. Label: 5chars

Thanks in advance

PaulC

 

[David R]

The InStr() function will help you out. It gives the place positioning of a certain character/string of characters. This combined with your knowledge of the length of the labels should let you parse your memo field.

When you say "have no choice", are you getting this data in a imported text file? Or is someone entering it in a memo field this way? Surely if you show them how easy it is ...oh, stop laughing.

 

[cpod]

You have to strip them off one at a time. The string doesn't get any longer than the three field I hope - each expression gets a little hairyer.

Expr1: Left([yourfieldname],InStr([yourfieldname],":")-1)

Expr2: Mid([yourfieldname],InStr([yourfieldname],[expr1])+Len([expr1])+2,Len([expr1])+InStr(Len([expr1]),[yourfieldname],":")+1)

Expr3: Mid([yourfieldname],InStr([yourfieldname],[expr2])+Len([expr2])+2,Len([expr2])+Len([expr1])+InStr(Len([expr1])+Len([expr2]),[yourfieldname],":")-(Len([expr2])+Len([expr1]))-4)

Expr4: Mid([description],InStr([description],[expr3])+Len([expr3])+2)

[This message has been edited by cpod (edited 04-18-2002).]

 

[David R]

Well, it doesn't have to get QUITE that hairy. Here's an example I did recently that works rather cleanly: http://www.access-programmers.co.uk/ubb/Forum3/HTML/003457.html

 

[PaulC999]

Thanks to both of you.

With your help and a little luck I have solved the problem.

As suggested I used "InStr()" to find the start of text and noticed that in every case the imported data had the same number of chars (although some were somehow hidden).

This allowed me to use "Mid()" to ruturn only the data required.

The only problem is that Mid()is returning some unwanted data if the string that InStr()
is looking for is absent, I'm still working on that bit.

Thanks again
PaulC