Use variable input from Form to write a select query

[tkl]

How to write a query with variable field name.

Lets suppose:



I want to find Sum([X]) where X is a field name(from table) entered by the user. Is it possible to do that in design view or you need to write a code for that.

[orange]

Sounds like you would need code, but perhaps you could give a realistic sample with vales to show what you need.

[tkl]

Ok.

I have a table with different fields like Balance, Advance, Term, Payables etc.

I want to calculate the sum of specifically that field which the user enters on a form.

Now I have written a query : Sum([Form]![Field])
So if the user inputs "Advance" on the form, by running the query I should get sum for Advance. I thought just putting [Form]![Field] in expression would work as its value would effectively be "Advance", so the query will interpret it as Sum(Advance). But it isn't working.

[orange]

I don't think that will work. As an alternative suppose you have a list of the fields that could/should be Summed

I have a table with different fields like Balance, Advance, Term, Payables etc.

Now suppose you had a form that the appropriate user(s) could access, and that form had a List of Choices of Which Form/Report to run

The List of Forms or reports could be in a drop box(combo)

Based on the selection chosen, you run the related

Form/Report based on the value selected by the user.

[tkl]

Actually my requirement is something different. I have to calculate 5 sums Balance, Advance, Term, payables etc. I hav written queries to calculate sum of these fields. But everyday I get new files for which I have to calculate these sums. These names can be bal or bal_1 for balance, adv or advn for advance, essentially different field names of same parameter in different files. Every time I have to change the names of fields in the query which are there in that particular file.

I thought of writing a general query where anyone could just enter the field names on a form and the query would pick it from the table and run it.
I don't whether I am making any sense or not.

[orange]

Why do the names of fields change on a daily basis?
Tell us more about the processes involved.

It sounds a little like a lack of discipline or some protocols.
One of the advantages of automation is applying standard processes to standard standard formats. If you are changing field names on a daily basis, or using field names based on the whims of the supplier, then you are defeating yourself.