Left() Function

[mike wild]

Using Left function to select the field name

I am constructing a where statement within a update query.

this is what i have so far:

DoCmd.RunSQL "update bets SET bets.[bettotal] = [bet1] * odd /2 + [bet1] where (winruner2race1 = left([r1n1],4);"

it is the where statement i am having problems with.
I have 8 fields called: r1n1, r1n2, r1n3,r1n4, r1n5, r1n6, r1n7 and r1n8.

What i am trying to do is this:
If for example the value of winruner2race1 = 4 then i want to find the value of field r1n4.

i am not able to change the filed names, since i also have
r2n1, r3,n1, etc, etc, in a 8 X 8 grid of cells

 

[Rich]

You need to normalise your data, Access is not a spreadsheet