Variable field name in query or table (1 Viewer)

[Arvidsson]

Hello,

is it possible to name a field in a query or a table with a variable name?

For example:
I want to choose in listbox "fieldname" the name of the field.

And the query field name looks like:

forms![testform]![fieldname]: FieldOfTheTable

Unfortunately it doesnt work.

Also if I save the choosen fieldname in a vba function.

fieldNameFunction(): FieldOfTheTable

This doesnt work, too.

Does someone have an idea?

Thank you very much in advance!

 

[vbaInet]

Public Function [COLOR="Blue"]MyFunction[/COLOR](FunctionName As String) As Variant
    [COLOR="blue"]MyFunction [/COLOR]= Eval(FunctionName)
End Function

Use MyFunction() in your query.

There's another way but I can't remember the name of the method.

 

[vbaInet]

I remember it now but it doesn't apply here.

 

[Arvidsson]

I copied your VBA Code into a module and used this sql code

SELECT tblIndizes.Indizes_Symbol, tblIndizes.Indizes_Region, tblIndizes.Indizes_Land AS MyFunction()
FROM tblIndizes;

But a error message appears:

The selected code includes a reserved word or an argument name that has a wrong punctuation or was not entered

 

[vbaInet]

Oh you're trying to use the resultant string from the function as the name of a field in a query? It can't be done that way.

You need to do it in the querydef.

 

[Arvidsson]

Yes, you are right.

I have never worked with querydef.

Can you give me an example please?

That would be very nice

 

[Arvidsson]

Or is it easier to name the captions of a form with my "variable field name"?

querydef looks like a big vba code

 

[vbaInet]

The form way would make better sense.

For the querydef, if you want to alias a field name you'll have to get the SQL of the querydef and do some string manipulation using Mid(), Instr() and Replace() to find that field name and replace it. Or you can also change the Caption property of a field but the field name will remain the same.

 

[Arvidsson]

Thank you very much vbaInet.

I decided to do the form variant

 

[vbaInet]

Good man (or woman)!

By the way, what's the reason of doing this? Is the form being opened as a Datasheet?

 

[Arvidsson]

Man

Yes a datasheet, and one column is calculated and I have some different calculation methods.
I can choose the calculation method in a other form, and in this "datasheet form" I want to see what calculation method is used in the column.

I hope I could it explain clearly

 

[vbaInet]

I hope I could it explain clearly

Not really! But I'll try to understand.

So you're using the Caption of a label as the Column Header?

 

[Arvidsson]

I will use the calculation method and the setting as header of the column.

The column includes a calculation of an moving average.
And for this i can choose the type (simple MA, weighted MA) and the time setting (2-500 days).

And if I get the results in the form, I want to know which caluclation method was used.

For example:

test_Name | test_Date | test_SimpleMA_10
-------------------------------------------
datapoints...

and if I calculate with the weighted MA and time period 30:

test_Name | test_Date | test_WeightedMA_30
-------------------------------------------
datapoints...

Maybe this helps

 

[vbaInet]

Maybe this helps

Maybe

Here's a suggestion. Why not create a small box in the form's header that will show which calculation was used instead of putting in the column header? Just like you have "Keys" when you're describing datapoints in a chart or graph.

 

[Arvidsson]

That is what i intend to do

I already have an new thread (http://www.access-programmers.co.uk/forums/showthread.php?p=1366372#post1366372)

Maybe you can have a brief look into

 

[vbaInet]

I've just had a look

By the way, there are so many of these --> in this thread.