O'Reilly logo

Access 2002 Programming by Example by Bob Villareal

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using Query by Table Example for If Then Scenarios

One solution is to use a string of nested IIF functions in an update query. It would look like the following:

IIF([Incurred]>=1 and ([Incurred]<=500,[Level]=1,IIF([Incurred]>=501 and 
[Incurred]<=1000,[Level]=501,IIF([Incurred]>=1001 and [Incurred]<=5000, [Level]=1001,IIF(
[Incurred]>=5001 and [Incurred]<=10001,[Level]=5001, IIF([Incurred]>=10001 and
 [Incurred]<=25000,[Level]=10001, IIF(Incurred]>=25001 and [Incurred]<=50000,[Level]=25001,
 IIF([Incurred]>50000,[Level]=50001 

This solution has ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required