Convert Field Value String to Actual Field in Expression

[Zontar]

I'm trying to improve performance on a query, and I was wondering if this is possible in Access.

Let's say I have a table [fieldtable] with one text column [field1] and one row.

In that column, I have a field name from another table [TransactionTable]. This fieldtable gets updated from a form, but it never has more than one row in it.

So an example value in my [fieldtable] look like this:

[TransactionTable].[Name]

If I put both tables in my query, without a link, I want to be able to call the field in my [TransactionTable] based on the string shown in my [fieldtable].



So my expression in this example query would be

TransField: [fieldtable].[field1]

which would effectively give me this:

TransField: [TransactionTable].[Name]


This does not work, but I'm looking for some way to make it work, if possible. Any ideas?

[MMcCarthy]

Don't put the fieldtable in your query. You can refer to it by:

Dlookup("[Field1]","[FieldTable]")

I'm trying to improve performance on a query, and I was wondering if this is possible in Access.

Let's say I have a table [fieldtable] with one text column [field1] and one row.

In that column, I have a field name from another table [TransactionTable]. This fieldtable gets updated from a form, but it never has more than one row in it.

So an example value in my [fieldtable] look like this:

[TransactionTable].[Name]

If I put both tables in my query, without a link, I want to be able to call the field in my [TransactionTable] based on the string shown in my [fieldtable].



So my expression in this example query would be

TransField: [fieldtable].[field1]

which would effectively give me this:

TransField: [TransactionTable].[Name]


This does not work, but I'm looking for some way to make it work, if possible. Any ideas?

[Zontar]

Don't put the fieldtable in your query. You can refer to it by:

Dlookup("[Field1]","[FieldTable]")

Thanks, that Interesting, I didn't know about DLookup. I tried it, but I am still getting the "Value" of the field and not the actual field.

When I tried this, all my rows still said

[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]

When I wanted them to say:

Bob Parker
Mary Williams
John Smith
Randy Jones
Lana Maxwell

In other words, the "Name" field from the Transaction Table.

[MMcCarthy]

Zontar,

Where are you referencing the value [TransactionTable].[Name]

In a query or on the form?

Thanks, that Interesting, I didn't know about DLookup. I tried it, but I am still getting the "Value" of the field and not the actual field.

When I tried this, all my rows still said

[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]

When I wanted them to say:

Bob Parker
Mary Williams
John Smith
Randy Jones
Lana Maxwell

In other words, the "Name" field from the Transaction Table.

[Zontar]

Zontar,

Where are you referencing the value [TransactionTable].[Name]

In a query or on the form?

mmccarthy,

I am using a form, but my question is only regarding a query and a table.

The string "[TransactionTable].[Name]" is what I typed as the value in the first row of my FieldTable Table.

I hope to then use that value in the FieldTable in my query of the TransactionTable.

Basically, I want to Group By whatever field name happens to be in the FieldTable, if that makes sense.

I have a lot of potential field names that I can group on in my query. Instead of housing them all in nested IIF statements (which is too long of an expression anyway, and slows down the query quite a bit) I would like to use this alternative solution, if it's possible.

[MMcCarthy]

I'm sorry I really don't understand what you're trying to do.

However, if you store "[TransactionTable].[Name]" as a value it will be a string representing the field in the table. But I don't understand how you are referencing it. Or maybe I just don't understand the question you're asking.

mmccarthy,

I am using a form, but my question is only regarding a query and a table.

The string "[TransactionTable].[Name]" is what I typed as the value in the first row of my FieldTable Table.

I hope to then use that value in the FieldTable in my query of the TransactionTable.

Basically, I want to Group By whatever field name happens to be in the FieldTable, if that makes sense.

I have a lot of potential field names that I can group on in my query. Instead of housing them all in nested IIF statements (which is too long of an expression anyway, and slows down the query quite a bit) I would like to use this alternative solution, if it's possible.

[Zontar]

I'm sorry I really don't understand what you're trying to do.

However, if you store "[TransactionTable].[Name]" as a value it will be a string representing the field in the table. But I don't understand how you are referencing it. Or maybe I just don't understand the question you're asking.

Sorry this is so hard to explain. Perhaps I should give a more complete explanation instead of trying to simplify it.

Basically, I would like to design a query that will Group By multiple fields depending on what fields a user selects in a Combo Box.

Since I would like two levels of grouping, it just isn't feasible to have hundreds of queries for every possible combination of groupings.


My FieldTable looks like this:

Field1
[TransactionTable].[Name]

Instead of [TransactionTable].[Name], the [Field1] field could also have the values

[TransactionTable].[Owner]
[TransactionTable].[Company]
[TransactionTable].[Facility]
[TransactionTable].[Location]

- depending on what the user selects in the Combo Box.



There are about 15 other fields in the transaction table that I want to be able to Group By in my query, with 2 levels of groupings. Instead of setting up a query for every possible combination, I would love to be able to do it in just one happy query.



So on my form, there is a combo box where a user will select which field to Group By in the query. I tried doing nested IIF statements in my query, like

SelectedGroup1: IIF([Forms]![Viewer]![Group1]="No Grouping", "No Grouping", IIF([Forms]![Viewer]![Group1]="Name", [TransactionTable].[Name], IIF([Forms]![Viewer]![Group1]="Facility", [TransactionTable].[Facility], IIF([Forms]![Viewer]![Group1]="Location", [TransactionTable].[Location], IIF([Forms]![Viewer]![Group1]="Company", [TransactionTable].[Company], IIF([Forms]![Viewer]![Group1]="Month", [TransactionTable].[Month].......

and so on and so on...

(Selected Group2 would be about the same, only it would reference a different control on the form, and be bound to a different table.)

However, I get an error message saying that my expression is too long. If I shorten it to only have a limited number of fields, the query is just so much slower than if I were grouping by the same field every time.

In order to get around this, I would like to do what I have asked about above. Basically, my query would Group By a field name that the user has selected in the combo box. I tried putting the Expression

SelectedGroup1: [FieldTable].[Field1]

which I hoped would equal [TransactionTable].[Name] and therefore reference that table.

in the query, but my results just looked like this:

[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
....

I tried using your suggestion of the DLookup, but had the same problem. There doesn't seem to be any way to trick Access into recognizing a string value in a field as a field itself, or is there?

[MMcCarthy]

It sounds like you're trying to do dynamic querying. I've done it myself a few times and I know how complicated it can get.

Firstly you need as you said a table of field names. Normally, I create one overall query. I do this by building up my query. e.g. Build query 1 then build query 2 based on query 1 and other table(s) and so on. This allows me to create calculated fields like Count([field]) and [field]-[field]. In building these queries I establish a column name for each calculated value as well as the table field names. I then end up with a query representing all choices I want to give the user. I build my field table based on the columns in the final query. Any grouping levels are built into the query as I build it.

I can then create (or allow the user to create) any select query involving these fields and the query will select all fields from the query rather than a series of tables.

As I said, I've done this a number of times and the way you're trying to do it isn't really feasible in my experience. Maybe one of the other experts can help you out with it.

If I'm wrong in what you're trying to do, please let me know.

In answer to your final question:

>I tried using your suggestion of the DLookup, but had the same >problem. There doesn't seem to be any way to trick Access into >recognizing a string value in a field as a field itself, or is there?

You would have to represent the TableName and Fieldname as two separate strings. Even then it would depend on how you called them.

If your table had two fields instead of one. One for the tablename and one for the field name. See basic query below as example of how to call them.

"SELECT [" & [Field2] & "] FROM [" & [Field1]

This would be passed to a string and the string then would make up the query.

Sorry this is so hard to explain. Perhaps I should give a more complete explanation instead of trying to simplify it.

Basically, I would like to design a query that will Group By multiple fields depending on what fields a user selects in a Combo Box.

Since I would like two levels of grouping, it just isn't feasible to have hundreds of queries for every possible combination of groupings.


My FieldTable looks like this:

Field1
[TransactionTable].[Name]

Instead of [TransactionTable].[Name], the [Field1] field could also have the values

[TransactionTable].[Owner]
[TransactionTable].[Company]
[TransactionTable].[Facility]
[TransactionTable].[Location]

- depending on what the user selects in the Combo Box.



There are about 15 other fields in the transaction table that I want to be able to Group By in my query, with 2 levels of groupings. Instead of setting up a query for every possible combination, I would love to be able to do it in just one happy query.



So on my form, there is a combo box where a user will select which field to Group By in the query. I tried doing nested IIF statements in my query, like

SelectedGroup1: IIF([Forms]![Viewer]![Group1]="No Grouping", "No Grouping", IIF([Forms]![Viewer]![Group1]="Name", [TransactionTable].[Name], IIF([Forms]![Viewer]![Group1]="Facility", [TransactionTable].[Facility], IIF([Forms]![Viewer]![Group1]="Location", [TransactionTable].[Location], IIF([Forms]![Viewer]![Group1]="Company", [TransactionTable].[Company], IIF([Forms]![Viewer]![Group1]="Month", [TransactionTable].[Month].......

and so on and so on...

(Selected Group2 would be about the same, only it would reference a different control on the form, and be bound to a different table.)

However, I get an error message saying that my expression is too long. If I shorten it to only have a limited number of fields, the query is just so much slower than if I were grouping by the same field every time.

In order to get around this, I would like to do what I have asked about above. Basically, my query would Group By a field name that the user has selected in the combo box. I tried putting the Expression

SelectedGroup1: [FieldTable].[Field1]

which I hoped would equal [TransactionTable].[Name] and therefore reference that table.

in the query, but my results just looked like this:

[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
....

I tried using your suggestion of the DLookup, but had the same problem. There doesn't seem to be any way to trick Access into recognizing a string value in a field as a field itself, or is there?

[Zontar]

mmccarthy,

Thanks for your help.. I am excited about the possibility of doing Dynamic Querying in MS Access, but I don't really know where to go from here. I tried some of the stuff you mentioned, but wasn't able to pass a SELECT statement into the query so it would Execute the SELECT statement, and not just display the SELECT statement.

I'm assuming this has to be done in VB code, which I'm not opposed to, I just usually can only work with an example.

If anyone has any good examples of how I can do Dynamic Querying, I would really appreciate it.

[MMcCarthy]

In the VBA code you need to create a query definition, probably on a button click. In the following example the list of fields is called List0. The Lists mult select property should be set to simple:

Private Sub cmdRunQuery_OnClick()
'This will create the query "My_Query"
Dim qdfNew As DAO.QueryDef
Dim strSQL as String
 
    strSQL = buildQuery ' build the query - see below
    With CurrentDb
        '  createquerydef command line follows
        Set qdfNew = .CreateQueryDef("My_Query", strSQL)
        qdfNew.Close
        Set qdfNew = Nothing
        .Close
    End With
End Sub
 
Function buildQuery() As String
' Function to build the query
Dim valSelection As Variant
Dim selectSQL As String
Dim strValues as String
 
    strValues = ""
    selectSQL = "SELECT "
    'Go throught each selected 'record' in listbox
    For Each valSelection In Me.List0.ItemsSelected
        'Save value to appropriate text box
        strValues = strValues & "[" & Me.List0.ItemData(valSelection)  & "], "  
    Next valSelection
 
    ' remove the last , and space
    selectSQL = Left(selectSQL, Len(selectSQL) - 2)
    selectSQL = selectSQL & " FROM QueryName;"
 
    buildQuery = selectSQL ' return the statement
 
End Function
 
 

mmccarthy,

Thanks for your help.. I am excited about the possibility of doing Dynamic Querying in MS Access, but I don't really know where to go from here. I tried some of the stuff you mentioned, but wasn't able to pass a SELECT statement into the query so it would Execute the SELECT statement, and not just display the SELECT statement.

I'm assuming this has to be done in VB code, which I'm not opposed to, I just usually can only work with an example.

If anyone has any good examples of how I can do Dynamic Querying, I would really appreciate it.

[polyetilen]

DLookUp is working nice just use Forms() to extract values from forms and insert it as field name, in example:

Combo name is [SelectName]

so extract from combo selected value with:

Forms("Same form")("[SelectName]")
 

then insert this value to DLookUp as field name like this:

"[" & Forms("Form with combo")("[SelectName]") & "]"
 

this is recognized as field name

and insert in to DLookUp:

=DLookUp("[" & Forms("Form with combo")("[SelectName]") & "]";"[FieldTable]";"[Name] = " & Forms("Form with combo")("[SelectName]"))
 

Last

"[Name] = " & Forms("Form with combo")("[SelectName]")
 

it is filter and [Name] can also be construction of field name generated selected form.