J
John
I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields are:
[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...
I tried creating an update query with this in the "Update To" row:
iif(val([tblActMap]![Act])<=8999,"08",
iif(val([tblActMap]![Act])>=9000 and val([tblActMap]![Act])<=9999,"09",
iif(val([tblActMap]![Act])>=10000 and val([tblActMap]![Act])<=11999,"10",
iif(val([tblActMap]![Act])>=12000 and val([tblActMap]![Act])<=15999,"12",
iif(val([tblActMap]![Act])>=16000 and val([tblActMap]![Act])<=17999,"16",
iif(val([tblActMap]![Act])>=18000 and val([tblActMap]![Act])<=19999,"18",
iif(val([tblActMap]![Act])>=20000 and val([tblActMap]![Act])<=22999,"20",
iif(val([tblActMap]![Act])>=23000 and val([tblActMap]![Act])<=25999,"23",
iif(val([tblActMap]![Act])>=26000 and val([tblActMap]![Act])<=35999,"26",
iif(val([tblActMap]![Act])>=36000 and val([tblActMap]![Act])<=41999,"36",
iif(val([tblActMap]![Act])>=42000 and val([tblActMap]![Act])<=49999,"42",
iif(val([tblActMap]![Act])>=50000 and val([tblActMap]![Act])<=51999,"50",
iif(val([tblActMap]![Act])>=52000 and val([tblActMap]![Act])<=53999,"52",
iif(val([tblActMap]![Act])>=54000 and val([tblActMap]![Act])<=55999,"54",
iif(val([tblActMap]![Act])>=56000 and val([tblActMap]![Act])<=56999,"56",
iif(val([tblActMap]![Act])>=57000 and val([tblActMap]![Act])<=58999,"57",
iif(val([tblActMap]![Act])>=59000 and
val([tblActMap]![Act])<=59999,"59")))))))))))))))))
LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).
Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....
Any suggestions?
values deplending on the value of a cost code. The table fields are:
[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...
I tried creating an update query with this in the "Update To" row:
iif(val([tblActMap]![Act])<=8999,"08",
iif(val([tblActMap]![Act])>=9000 and val([tblActMap]![Act])<=9999,"09",
iif(val([tblActMap]![Act])>=10000 and val([tblActMap]![Act])<=11999,"10",
iif(val([tblActMap]![Act])>=12000 and val([tblActMap]![Act])<=15999,"12",
iif(val([tblActMap]![Act])>=16000 and val([tblActMap]![Act])<=17999,"16",
iif(val([tblActMap]![Act])>=18000 and val([tblActMap]![Act])<=19999,"18",
iif(val([tblActMap]![Act])>=20000 and val([tblActMap]![Act])<=22999,"20",
iif(val([tblActMap]![Act])>=23000 and val([tblActMap]![Act])<=25999,"23",
iif(val([tblActMap]![Act])>=26000 and val([tblActMap]![Act])<=35999,"26",
iif(val([tblActMap]![Act])>=36000 and val([tblActMap]![Act])<=41999,"36",
iif(val([tblActMap]![Act])>=42000 and val([tblActMap]![Act])<=49999,"42",
iif(val([tblActMap]![Act])>=50000 and val([tblActMap]![Act])<=51999,"50",
iif(val([tblActMap]![Act])>=52000 and val([tblActMap]![Act])<=53999,"52",
iif(val([tblActMap]![Act])>=54000 and val([tblActMap]![Act])<=55999,"54",
iif(val([tblActMap]![Act])>=56000 and val([tblActMap]![Act])<=56999,"56",
iif(val([tblActMap]![Act])>=57000 and val([tblActMap]![Act])<=58999,"57",
iif(val([tblActMap]![Act])>=59000 and
val([tblActMap]![Act])<=59999,"59")))))))))))))))))
LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).
Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....
Any suggestions?