Cardbox Talk


CardboxForumsCardbox Talk > "Macro for manipulating 'large' number of numeric data fields"

Macro for manipulating 'large' number of numeric data fields

Seeking a neat macro to calculate large numbers of data within one record

Posted By Post

Chris H

6-Oct-2008 08:06

My database record has 92x3 fields in the format AA01, AB01, AC01 through to AA92, AB92, AC92. Each 'set' of same digit fields needs to have the 'formula' AA*AB=AC applied and the results formatted. Finally the total of AC01 to AC92 to be entered into a different field. I have constructed a long-winded 'brute-force' VBScript that does all of the above but am looking for advice to make this more elegant. Any ideas??


6-Oct-2008 08:32

I suppose you did not program for every AC field a separate calculation.
For this kind of stuff you better can make a one function for all.
What you mean then by "more elegant"?

Charles Welling

6-Oct-2008 08:37

Chris, perhaps this might be what you mean by more elegant. There's a single calculation, but the fieldnames change by means of a simple counter.

for counter = 1 to 92
if counter < 10 then
fieldAA = "AA0" & counter
fieldAB = "AB0" & counter
fieldAC = "AC0" & counter
fieldAA = "AA" & counter
fieldAB = "AB" & counter
fieldAC = "AC" & counter
end if

Fields(fieldAC) = Fields(fieldAA) * Fields(fieldAB)

Chris H

6-Oct-2008 11:29

Thanks Bert & Charles for your speedy responses,
By 'more elegant', I mean a methodology that does not require 92 separate calculations (and lines of script) plus lines for any variable definitions, formating of results etc.
Charles' solutions looks good but I will have to experiment to see if I can make it work. I may be some time.....

© 2010 Cardbox Software Limited   Home