variable as field name in query?

[gtroiano]

what i am trying to do is change the field name(column header name) in a select query. instead of something like this: Sum(qrySource.PreviousAmount)AS SumOfPreviousAmount [/red]
i would like "SumOfPreviousAmount" to be a specific string stored in "qrySource". is this possible? i assumed it would be something along the lines of Sum(qrySource.PreviousAmount) AS " & PreviousHeading & " [/red]
but it doesn't work. please help!

jerry

 

[rorymurray]

You could store the name you want to call it in a config table, then access it using a dlookup. So have a config table with config id and value, eg
configID Value
1 "Sum of Previous amount"

then in the query put:
Sum(qrySource.PreviousAmount) as
Dlookup("[config]", "Value", "[configID]=1&quot

Look for help on DLookups if you have any trouble with my example.

 

[rorymurray]

Actually I just tried that and I don't think it will work, unless you manage to use that method to build the sql string, then execute the sql.

 

[LittleSmudge]

i would like "SumOfPreviousAmount" to be a specific string stored in "qrySource". is this possible?

As qrySource evidently yields a multi record DynaSet then how are you going to tell Jet which record to look at ?



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[gtroiano]

here's the skinny. i have a form with a sub form on it displaying spending information for the current year and the previous year. since, next year, the current year information will go to the previous year side and new information will be entered for the new current year(if that isn't too confusing!), i need to have the headings change above each set of information on the form. so i have a seperate table that holds the two headings, "2000-2001" and "2001-2002", respectively.

jerry.

 

[KenReay]

Hi

I think you need to build your SQL in Code (you can cut and paste if from the querybuilder if you want to),

and edit the code which builds the strings so

strSQL = "SELECT ..blah .. blah, Sum(X) AS " & strYear & " FROM ...blah blah"

then
SET QDF = Db.QueryDefs("YourQueryName&quot
qdf.SQL = strSQL

where strYear is a string containing your year heading (eg 2002), beware of having anything in strYear which not an acceptable column name!!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[gtroiano]

i have no idea where to put said code. perhaps i am going about this the wrong way.
i have a bar graph on a report and a query as the row source for this graph. i am trying to make the graphs's legend dynamic. since the legend labels is the column heading of the chart's datasheet which in turn is defined in the query( Sum(qrySource.PreviousAmount) AS SumOfPreviousAmount ), "SumOfPreviousAmouont" being the current legend label for the first series of data. how should i go about changing the legend dynamically?

jerry.

 

[KenReay]

Hi

Have not used bargraph on report, so I cannot be sure, but in the on open event of the report I think, faling that since it is making a 'permenat' change to to query def, in the button click which calls the report should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[KenReay]

Hi

Have not used bargraph on report, so I cannot be sure, but in the on open event of the report I think, failing that since it is making a 'permenant' change to to query def, in the button click which calls the report should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[gtroiano]

ok, i hate access. i was able to modify the row source of the graph through code when it's on a form but i can't modify it when it's on a report. for the love of god!!!! why?

jerry.