Parse a field and Create a query

[DogLover2006]

Hello,
I have a table called SPF_2003_2004 it has a field in it called REQ it is a memo field (which are about 20 different names). This data was pulled from another application. I created a query that parse but eceeded resources. So now I am looking for code that will parse it and then create a query form that. The boss does not want to make another table in the database. Just a query that will be used to create a report that show each name, amount, total amount, and count. I needed this like yesterday. I have been working on this for day. Please respond asap. Thank you

Data:
REQ:
AUBRY, CARROZZA, CLARK, COHEN-M, COOK, GIANARIS, GRODENCHIK, LAFAYETTE, MARKEY, MAYERSOHN, MCLAUGHLIN, NOLAN, PERALTA, PHEFFER, SCARBOROUGH, SEMINERIO, TITUS, WEPRIN
AMT REQ:
500.00
SingleReq: False

 

[markphsd]

Use the split function to parse in code.


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[DogLover2006]

markphsd,

will this split a query? or just the table and How would I go about that if it is a query. I do not need to do a table because the boss does not want another table in the database. thank you

 

[markphsd]

Who is your boss that he doesn't want another table in the database? That's putting unecessary restrictions on your project. Making another table might not be the right solution, and if it is you should identify why.

I was saying that you could use the split function with vba to createa multiple records for the one memo field.

You can't use the split function to create multiple records in SQL. What the split function does is parse a string into an array based on a common delimeter. From that you could easily create a new recordset in code, in a table, or just put it directly into the report, which is your goal.

Dim sAmountReq as string
Dim sContactReq as string
dim alist() as string
dim i as long

sAmountReq = "500.00"
sContactReq = "AUBRY, CARROZZA, CLARK, COHEN-M, COOK, GIANARIS, GRODENCHIK, LAFAYETTE,"

alist() = split(sContactReq, ",")

for i = 0 to ubound(alist())
Debug.print alist(i) & " " & sAmountReq
next i





Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.