Select variable column name. 1

[ponderdj]

I have a table with 12 fields representing charges for each month. I have a report that I want to be fed the sum of the charges from last month. I don't want to update the query every month, so I'm trying this:

SELECT (SELECT Sum(Format(Now()-30,'mmm') & "_CHARGE")FROM TBL_CHARGEYTD) AS DEC_CHARGE1
FROM TBL_CHARGEYTD;

This is giving me a "data mismatch in criteria expression" error because it can't sum the string "DEC_CHARGE" . The field name is DEC_CHARGE but it isn't recognizing that.

Any ideas?

 

[PHV]

Perhaps something like this (in the query grid) ?
DEC_CHARGE1: DSum(Format(Now()-30,"mmm") & "_CHARGE","TBL_CHARGEYTD")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

PHV,

You're pretty good. Do you think it's impossible to do without using DSum?

Thanks

 

[PHV]

Another way (typed, untested):
Alias: Sum(Choose(Month(Now()),DEC_CHARGE,JAN_CHARGE,...,NOV_CHARGE))

Anyway, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

PHV,

I'm familiar with normalization, I've just chosen to construct one of my tables in this manner. Maybe I should rethink that decision to avoid problems like this.

Thanks again.