Is it possible to run Concat function multiple times?

[eefutah]

I'm using the Concat function from this thread, and I'm wondering if it's possible to run the function multiple times, so it won't save what's been passed by previous records with the same IOSC?
(I'm pretty new to this, so I apologize if the answer is obvious).

An alternative answer which better displays the flexible nature of this code, is included below. Notice this doesn't affect the calling code (SQL) at all.

Paste this function into a module then run a query with the SQL below.

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String

    If strGroup = strLastGroup Then
        strItems = strItems & ", " & strItem
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    Concat = strItems
End Function

SELECT IOSC,
  Max(Concat(IOSC, Feature)) AS Features
FROM [YourTable]
GROUP BY IOSC

Example

TABLE1:
IOSC: FEATURE:

00029 LH
00029 SWFTERM
00029 WATS
00031 1PTY
00031 BUS
00031 FR
00031 LS
00031 SWFBOTH
00031 TC
00573 FAXTHRU
00963 1PTY
00963 BUS
00963 FR
00963 LS
00963 SWFBOTH
00963 TC

Function Output from TABLE1 (works like a charm from what was provided in the thread!)

IOSC: FEATURE:
00029 LH,SWFTERM,WATS
00031 PTY, BUS, FR, LS, SWFBOTH, TC
00573 FAXTHRU
00093 1PTY, BUS, FR, LS, SWFBOTH, TC

But, if I run the function immediately after for the following info below, this is what I see:

TABLE2

IOSC: AGNCY:

00029 DWQ
00029 UGS
00029 DWQ
00031 DWQ
00031 DWQ
00031 UGS
00031 UGS
00031 DWQ
00031 DWQ
00573 DWQ
00963 DWQ
00963 DWQ
00963 DWQ
00963 DWQ
00963 DWQ
00963 DWQ

Function Output from TABLE2. (Info from the Function Output from Table1 is still retained)
IOSC: FEATURE:
00029 LH,SWFTERM,WATS , DWQ,UGS
00031 PTY, BUS, FR, LS, SWFBOTH, TC, DWQ,UGS
00573 FAXTHRU, DWQ
00093 1PTY, BUS, FR, LS, SWFBOTH, TC, DWQ


Desired display

IOSC: FEATURE:
00029 DWQ,UGS
00031 DWQ,UGS
00573 DWQ
00093 DWQ

So, is it possible to run the function multiple times without retaining what's been passed by previous records with the same IOSC?

Thank you for your help!

[zmbd]

eefutah:

On first glance, it appears that you did not change the source of the input data from TABLE1 to TABLE2.

You've pulled this code out of context by not providing the source(s); thus, a tad difficult to troubleshoot.

We do not normally analyze another's code for suitability of purpose, I'm sure you can understand why... (hear the conversaton... "but you said")

[eefutah]

Thanks, zmbd -- appreciate you taking a quick look!

I'll keep playing with the function and database. I thought all I had to do was change the FROM [YourTable] from the query provided in my original post.

In the database I can get the function and query listed above to run fine on both tables as long as I compact and repair right after I run the query. If I don't compact and repair before I run the query a second time on table2, the second query will produce a new output while also retaining what's been passed by previous records with the same IOSC from the previous query.

This is one of those things that would be useful to figure out in the future but not absolutely necessary for my work right now.

Thanks again for your thoughts.

[MikeTheBike]

Hi

Very intersting, but I would think it is the use static variable(s) that is the problem. Until the project is reset they will still retain their information from one query to the next and just keep on adding to the list ?!

MTB

[zmbd]

MikeTheBike may have the issue there with the "static" variables.

As I Said in my first reply - You have not provided the source for this code; thus, it is not an easy thing to determine what the programmer had in mind.

Are you closing the query after your first run or are you running the query once and then going into design mode to change the input table and then re-running the query?

[eefutah]

I forgot my question was removed from the original thread and posted as a new topic. This is the developer's original post:

http://bytes.com/topic/access/answers/569535-combining-rows-opposite-union

zmbd, I'm trying several ways. Like you guessed I've tried running the query once and then gone into design mode to change the input table and then re-run the query. I've also created 2 queries (1 for each table) and then run the queries back-to-back. It just seems quirky with my database, and I'm sure it's operator error (e.g., my fault). Again, I'm pretty new to this.

As a side note, during my searches last night I stumbled upon Allen Browne's concat function (http://allenbrowne.com/func-concat.html). It does a nice job without producing any snags for my database; it just takes a long time to run when there are lots of records.

[eefutah]

Also, MikeTheBike -- thanks for taking a look and for the response