splitting CSV field

[scottian]

I have a field in one of my tables that contains a string of text seperated by commas, is there a way to split this field by comma?

"My God! It's full of stars...

 

[DrSimon]

When you say that the field is separated by commas do you mean that the whole set of records is comma delimited or is there a single field within the input that contains commas? It may help if you give examples.
Simon Rouse

 

[scottian]

sorry simon

example

IssueNumber,1,Date,01.01.04,Name,A Name,LogInId, Name1A, Location,HeadOffice,IssueNote, TextTextTextTextTextText

this is recieved via an e-mail form that is in HTML format, ive managed to import it into one record but i have no idea how to split the field. I am trying to use the Instr function to locate the end of the field names but it returns a 0 when i use "IssueNumber," im guessing because yu can only use single characters to search for

"My God! It's full of stars...

 

[DrSimon]

There is a function called Split which (to quote help)"returns a zero-based, one-dimensional array containing a specified number of substrings". So basically you'd read the record use Split to put in into an Ary, then output that array into another table. Of course this would fall down if a comma was entered into the sub field as in Name ="Smith, John"
As an aside don't know why Instr is giving problems as you can certainly use it to search for >1 character
Simon Rouse

 

[scottian]

if i could use more than one character i.e

Expr1: InStr([Message],"IssueNumber")

this would prove to be more robust as the chances of someone using the field name within a text field would be less than someone using a comma

but as a i say it just returns a 0 when i use anymore than one character in the compare part of the instr function.

any help with this is much appreciated

"My God! It's full of stars...

 

[DrSimon]

scottian I'm confused about what you are doing, how the data is being held and what you want to do with it!
Instr - just to check I wasn't going mad, I have just run a query on a table with and then used Instr to find the position of a substring:
Field Filename: 2004\01\02\103243859.tif
Expression: Exp1: InStr([Filename],"\01\")
Returned 5

Is the entire data in your example held in a single field that you want to parse?

How are you trying to parse it are you useing VBA or trying to do it some other way?