Split a String 1

[Dineedshelp]

Hi,

I don't know how I am going to explain this one easily but I will give it a go!!!

I have a database linked to a sql database. Within this is a field that holds information that is set to hold the same information for every record, but might not as it isn't relevant.

For example:

Record 1 returns: has proof a , has proof b, has proof c

record 2 returns: has proof a , has proof c,

What I need to do is split this string down into different columns, but if a field is missing ie record 2 has no proof b, I need it to jump to the next column.

Commas seperate the info within the sting, and I have a list of all the column names I need.

Does anyone understand this?

 

[PaulBricker]

Your example would be easy to do but maybe not your actual data. Try something like this in three columns of your query when "str" in the name of your field that has the actual data.

Col1:IIf(Instr(InStr(1, str, "has proof a") > 0,Mid(str, 1, Len("has proof a")),"")

Col2:IIf(Instr(InStr(1, str, "has proof b") > 0,Mid(str, 1, Len("has proof b")),"")

Col3:IIf(Instr(InStr(1, str, "has proof c") > 0,Mid(str, 1, Len("has proof c")),"")


Paul

 

[PaulBricker]

Sorry, got an extra Instr () function in there. this is the first expression

IIF(InStr(1, str, "has proof a") > 0,Mid(str, 1, Len("has proof a")),"")

sorry about that

Paul

 

[Dineedshelp]

Thank you!!!!!!!!