How to treat a multi value delimited field as different record? 1

[hmstoo44dd]

I have a memo field in a table that holds multiple customer numbers delimited by a semicolon. When the form is saved it concatenates, delimits, and copies all values selected from a list box. I need to be able to create a report that shows each customer as a different record. I have used the split function before but can't see where it will help me. Can anyone help?

PS:This is only a click of a check box in Lotus Notes.

 

[Darrylles]

Hi hm,

You need vba: mid() and instr() to do this.

Regards,

Darrylle

p.s. This is MS Access which is a programming tool - not an end-user software title.

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[dhookom]

I don't agree with storing multiple values in a single field....

However, you might create a solution with SQL. I opened Northwind and added a Products field to the Categories table. I then added values into the new field like:
[tt][blue]
1;2
4;11;14
22;10;21
--etc--[/blue][/tt]

I then created a query with the following SQL which could be used as the record source for a report.

SELECT Categories.CategoryID, Categories.CategoryName, Categories.Products, ";" & [Products] & ";" AS Expr1, Products.ProductID, Products.ProductName
FROM Categories, Products
WHERE (((";" & [Products] & ";") Like "*;" & [ProductID] & ";*"))
ORDER BY Categories.CategoryID, Categories.CategoryName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]