concat string

[abenitez77]

How to I get a portion of a string into a variable?
I want to capture the table name of a recordsource. The problem is sometimes the recordsource is "Select * from mytable where ID = 6" or "Select * from mytable" or "mytable"

I want to put "mytable" in a variable for later use.

 

[pbaldy]

My first thought was that you could test first for a space, which the absence of would indicate just a table name. Then I remembered some people have table names with spaces in them.

So, I suppose you could test for the word "FROM". If it's not there, then there must just be a table name. If it is there, you can grab what follows, but of course you'll still have trouble figuring out where to stop. Again, the potential for a space in the table name means you can't just stop at the next space. You could theoretically have joined tables, what do you want then?

I envision a function that does the various testing, but you'd basically have to build it using trial and error to account for all the possibilities. I further envision it will be tedious. You'll use InStr(), Mid(), perhaps InStrRev(), perhaps others.

 

[abenitez77]

This worked, but there has to be a better way???

Mid(Mid("Select * from mytable where x = 1",instr(1,"Select * from mytable where x = 1", "from") + 5),1, instr(1, Mid("Select * from mytable where x = 1",instr(1,"Select * from mytable where x = 1", "from") + 5)," "))

 

[pbaldy]

I find nested statements difficult to create and edit, which is why I suggested a function. In any case, does that handle a space in the table name? Does it handle a table name by itself? I don't think so.

If there are joins, what do you want returned? I suspect that will return the first table, not the ones joined.

 

[abenitez77]

There won't be joins. I'm thinking I may use an array to do this..grab the value after from...

 

[pbaldy]

Again, does it handle a table name by itself, or a space in the table name?

 

[CJ_London]

This query will list the table names in your query - spaces 'n all

 SELECT MSysQueries.Name1 AS TableName, MSysQueries.Name2 AS AliasName
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysObjects.[Name])="[COLOR=red]query1[/COLOR]") AND ((MSysObjects.[Type])=5) AND ((MSysQueries.Attribute)=5));

Change the name of query1 to the name of your query.

This will return all the tables used in the main query plus their aliases if used. It will not return tables used in subqueries

Note: Accessing system tables should be done with care

 

[pbaldy]

Interesting, I learned something new today. It would only work on a saved query though, not SQL in the record source, correct (you'd need to know the super-secret saved name of the record source SQL)? Nor would it work on a plain table name. Neat trick though!

 

[CJ_London]

It would only work on a saved query though

True - but should meet the needs of the OP if he is still checking the thread. But

I use it in a function - it has a string parameter and returns a string of tables separated with ;

the function checks if there are any spaces in the string parameter (I don't use spaces) and if there aren't any it must be a saved query (I don't send tables through the function)- and if there are - it saves it as a querydef - and now you have a saved query.

I use it as part of a routine which provides a simple query builder for users to build their own queries for reporting and task management purposes - I don't allow users access to the navigation window/ribbon etc

with proper error control you can make the function recursive to pick up on subqueries as well if required

 

[MarkK]

This function solves it for the text the OP posted anyway . . .

Function GetTableName(RecordSource As String) As String
    Dim var
    var = Split(RecordSource, " FROM ")
    If UBound(var) = 0 Then
        GetTableName = RecordSource
    Else
        GetTableName = Split(var(1), " ")(0)
    End If
End Function

Private Sub Test1293847612930487()
[COLOR="Green"]'   tests the above function[/COLOR]
    Debug.Print GetTableName("Select * from mytable where ID = 6")
    Debug.Print GetTableName("Select * from mytable")
    Debug.Print GetTableName("mytable")
End Sub

 

[pbaldy]

True - but should meet the needs of the OP if he is still checking the thread.

I got the impression that the item to be evaluated was the record source property of a form or report, given the possibility of it being just the name of a table.

 

[abenitez77]

This function solves it for the text the OP posted anyway . . .

Function GetTableName(RecordSource As String) As String
    Dim var
    var = Split(RecordSource, " FROM ")
    If UBound(var) = 0 Then
        GetTableName = RecordSource
    Else
        GetTableName = Split(var(1), " ")(0)
    End If
End Function
 
Private Sub Test1293847612930487()
[COLOR=green]'   tests the above function[/COLOR]
    Debug.Print GetTableName("Select * from mytable where ID = 6")
    Debug.Print GetTableName("Select * from mytable")
    Debug.Print GetTableName("mytable")
End Sub

MarkK , thanks! this is exactly what I needed.