Here's the problem I'm having and I can't for the life of
me figure out what's wrong. I'm using Access 97.

For each month of data I have a separate table and then a
UNION query which consolidates all the months up to the
current month. Each month when a new table is created for
that month I need to automatically add a reference to the
new table into my UNION query's SQL statement. I also need
to check whether that reference has already been added.

Here is the simple algorithm that I use to do this:

Dim qdfFeed As QueryDef
Dim dbs As Database
Set dbs = CurrentDb

' this is the UNION query:

Set qdfFeed = dbs.QueryDefs("Feed to RSL")

Dim OldSQL, NewSQL String
OldSQL = qdfFeed.SQL

' this is the reference that I want to add:

Dim newName As String
newName = "Feed to RSL - Microfiche and Print - Jan 2002"

Dim newReference As String
newReference = "UNION ALL SELECT [BillType],
[RevenueTransit],[ProductID],[ClientID],[Volume],
[ChargeToUnit],[Period],[Year],[Original Period],[Original
Year] FROM [" & newName & "]"

' Now, I check whether that reference is in the SQL
statement:

If InStr(newName, OldSQL) = 0 Then

    ' remove the final ";" in the SQL statement
    NewSQL = Left$(OldSQL, Len(OldSQL) - 1)
    ' add the new reference
    NewSQL = NewSQL & newReference & ";"
    'assign the new SQL statement to "Feed to RSL"
    qdfFeed.SQL = NewSQL

End If

For some reason, this does not work - even if the
reference is already in the SQL statement, the computer
does not recognize this, and continues to add in a copy of
the reference anyways. Furthermore, when it adds in the
new reference, it fails to remove the terminating ';'
before adding the reference to the end of the SQL
statement.

Can anybody shed some light on why this might be
happening? Is the SQL statement too large a string for the
string processing functions? Or is there something else
I'm missing?

Any help would be appreciated! Thanks for all your time.

Mike

On Fri, 24 May 2002 07:12:29 -0700, "Michael Berger"

:-{(  Why? This seems to be a bad design on a couple of counts: storing data in tablenames; wrapping around from year to year; and of course, UNION queries are MUCH less efficient than a single table with a Month field (or just a date field).

If you insist on this (IMO) faulty design, I'd just have twelve tables
Jan-Dec; instead of *creating* a new table every month, just run an
append query to populate it. Your UNION would have all twelve months
in it all the time; in February it would be UNIONing January's data
with eleven empty tables.

But a much cleaner relational design would be to have ONE table;
you'ld just Append each month's data into it. With proper indexing
this will work fine; in fact, in almost any scenario I can imagine, it
will work much better than the UNION.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public