IIF and SWITCH (1 Viewer)

[rkisor]

Hi ,


I'm using Access 2010. It seems like IIF and Switch accepts only 13 to 15 conditions, above 15 both IIF and switch are not working. Its showing an error message as "Too complex". I have 362 conditions to complete. Is there any other function can support more than 300.

Please Help


Thanks,

KK

 

[Brianwarnock]

With many conditions I would write a function and use Select Case, however with that many I would, for ease of management, look to creating a table of the data and using DLookup

Brian

 

[rkisor]

Thank You for your response.

How to use select case or Dlookup in Build query section. Is there any example you can provide?

Thanks,
KK

 

[Galaxiom]

With 362 conditions required I expect your database structure needs to change.

Please explain why you need so many conditions.

 

[Pat Hartman]

A table is the simplest solution since it requires no code. It is also the most efficient since the database engine will be able to optimize the join. It is also upsizeable whereas the other options are not. Just create the xref table and join it to the main table in a query. So for example, if you want to translate a two-digit state code to the state name, join the address table to the state table on state_abbr and select StateName from the state table.

For lookup tables like this, you will need to use a left join rather than an inner join if the value is optional.

 

[rkisor]

Hi,

Here is the situation

Days Tenurity
7 Week 1
8 Week 1
15 Week 2
22 Week 3
29 Week 4
36 Week 5
43 Week 6
50 Week 7
57 Week 8
64 Week 9
71 Week 10
78 Week 11
85 Week 12
92 Week 13
99 Week 14
106 Week 15
113 Week 16
120 Week 17
127 Week 18
134 Week 19
141 Week 20
148 Week 21
155 Week 22

For example days 7 to 8 it should week 1, 9 to 15 is Week 2, 16 to 22 is week 3 like that it goes on.

I tried using IIF and Switch but it accepts only up to 15 conditions. Is there any other query to do better than this.

Please help me out.

Thanks
KK

 

[Galaxiom]

A simple formula could be used. I have not checked it all but the following seems likely.

Week = (Days -1)\7

 

[Brianwarnock]

Wouldn't it be

Week=int(days/7)

Brian

 

[MartynE]

Brian is right, when "Week = (Days -1)\7" what if it was day 1? 0/7 would cause an error.

 

[rkisor]

Hi Thank You So much, I try this out and let you know.

Thanks
KK