Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parse a field and Create a query

Status
Not open for further replies.

DogLover2006

Technical User
May 12, 2006
64
US
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,

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
 
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.
Providing Low Cost Powerful Point of Sale Solutions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top