Parse Query Results to Text String

[LarryDavidow]

I have found a few solutions that look like they will "Almost" work, but nothing that will actually solve it... so, here goes.

I am trying to take a query result like this:

OrderDetailID ProductName Qty
1 LeatherBag 2
2 PlasticBag 1
3 CottonBag 4

and, parse my result to a text field like this

2 X LeatherBag, 1 X PlasticBag, 4 X CottonBag

Relationship structure is as follows:

Table: Orders
Table: OrderDetail (Child Table to Orders)
Query: OrderDetailQuery (Based on OrderDetail Table with CustomerID as filter criteria)

The text field is actually going to be refrenced on a report for future use, so the field needs to be a value in the Orders table. Obviously, if someone goes into that order and adds a record, the text field needs to be updated to reflect the new item added.

This has actually been a lot harder to explain than I anticipated.

Please let me know if I need to do a better job explaining my problem!!

Larry

 

[lespaul]

why would you want to store a calculated field in a table (breaks normalization rules). In your query do:

SELECT Qty & " X " & ProductName from OrderDetail

will give you a result set of:

2 X LeatherBag
1 X PlasticBag
4 X CottonBag

I can't tell from your question if you really want a comma separated list of these items.

HTH

leslie

 

[LarryDavidow]

Thanks for the reply Leslie.

Not sure what you meant by "Calculated Field". None of those fields are necessarily calculated. And, yes, it is a comma, hence "Text Field".

I guess this could almost be called an Array, or maybe a recordset. Not enough knowledge... hence, this post

 

[lespaul]

Obviously, if someone goes into that order and adds a record, the text field needs to be updated to reflect the new item added.

If you are updating a field in another table when something in a different table is changed then it would be referred to as a calculated field. Think of it like an accounting program, if you have a table that stores the "balance" of each account, each time a entry is posted to that account in the journal file (the detail) then the balance has to be recalculated. Therefore, it's a calculated field.

What I would do is run the query above and then loop through a recordset and for each item in the recordset, add the value to a string: (not sure of the exact syntax would be something like this)

while not recordset.eof do
if strText = "" then
strText = ValuefromRecord
else
strText = strText & ", " & ValueFromRecord
end if

then you have a comma separated list of your items.

Search Forum705 for information on setting up the code to loop through the recordset.

HTH

leslie

 

[LarryDavidow]

Thanks for the idea, Leslie.

I'll play around with it and see what happens...