Code Help: Using Field Value to Call Column Name

[Legend1673]

I have two tables called "Current" and "Results". "Results" table has field "Code" which has a listing of all the column names from the "Current" table.

Current Table
A B C D E F
1
2
3
4

Results Table
Code CurrCount
A
B
C
D
E
F


I am trying to populate results.currcount with the number of records in the corresponding column of the Current table with a value > 0. So basically, I'm trying to identify the current.<column> using the value in results.code.

DoCmd.RunSQL "UPDATE Results " _


& "SET currcount = DCount(""*"", ""Current"", code > 0 ) "

Anyone able to help with the syntax or know of a better way??...

[rpeare]

don't use domain functions (dcount, dsum, davg, etc) in queries, they are horribly inefficient in that context.

I would use a crosstab query for this it seems the ideal solution.