Expression too complex in query expression

[murray83]

First Happy New Year and secondly Good Evening. now on to my conundrum

this is what i have type into the Criteria for my query

IIf([Forms]![frm_Main]![checkAct]=1,"Acton",IIf([Forms]![frm_Main]![checkAll]=1,"Allington",IIf([Forms]![frm_Main]![checkBasing]=1,"Basingstoke",IIf([Forms]![frm_Main]![checkBed]=1,"Bedford",IIf([Forms]![frm_Main]![checkBel]=1,"Belfast",IIf([Forms]![frm_Main]![checkBristol]=1,"Bristol",IIf([Forms]![frm_Main]![checkCrick]=1,"Crick",IIf([Forms]![frm_Main]![checkDar]=1,"Dartford",IIf([Forms]![frm_Main]![checkElstree]=1,"Elstree",IIf([Forms]![frm_Main]![checkEme]=1,"Emerald",IIf([Forms]![frm_Main]![checkGreen]=1,"Greenford",IIf([Forms]![frm_Main]![checkHams]=1,"Hams Hall"))))))))))))

this equals a total of 586 characters but from what ive read the limit should of been 255

my problem is when i try and carry on building the expression it gives me the attached fault. :banghead:

so my question is, is there a way round it for instance could i put the remainder in the "or" section in the query or perhaps a simpler way with less characters

cheers all

 

[pbaldy]

So the result will only ever be one city name? You could populate a textbox with your checkboxes and have the query get the value from there.

 

[murray83]

This is an inefficient and error prone way of doing this. Create a table that has all the possible values. Then use a combo box to select the one you want.

This method will be difficult to manage and will require YOU to make any additions should they be needed in the future, It is far better to allow users to be in control of their own value lists.

I've attached a sample database which is a little more complex but it is generic and I use it in virtually every new app I create and have done this for 40 years (since long before Access was invented). There are two tables, two forms, and two reports. If you like the way they look and act, you can simply import the 6 objects into your own application. The idea is to give the user control over his own lists. Obviously some applications have many more lists than others but in no case should you ever have to maintain them. As long as the list values do no control program flow, who cares how many cities are in a list?

Pat, thanks will give it a look and always welcome for easier ways

 

[arnelgp]

40 years! How old are you now, 41?

 

[murray83]

fixed it by doing the following pictures are attached

Added another column with yes/no tick box to my table then in my query removed the old criteria and just put =yes as you can see in the attached

so no longer need my massive long Iff statement and it works a charm

 

[The_Doc_Man]

Ah, Pat, that age is the wrong answer. You most recently celebrated the <?th> anniversary of your 29th birthday. That's what my stepdaughter has done for the last 10+ years.

 

[raziel3]

This is an inefficient and error prone way of doing this. Create a table that has all the possible values. Then use a combo box to select the one you want.

This method will be difficult to manage and will require YOU to make any additions should they be needed in the future, It is far better to allow users to be in control of their own value lists.

I've attached a sample database which is a little more complex but it is generic and I use it in virtually every new app I create and have done this for 40 years (since long before Access was invented). There are two tables, two forms, and two reports. If you like the way they look and act, you can simply import the 6 objects into your own application. The idea is to give the user control over his own lists. Obviously some applications have many more lists than others but in no case should you ever have to maintain them. As long as the list values do no control program flow, who cares how many cities are in a list?

Hi, Pat just a general question. Can an expression be too long and does having complex expressions 'slow down' MS Access performance.

I'm just asking because I worked with a database made with Access 97 and with the amount of data, when I ran a query it lagged a bit to return the expression results so I started to wonder if the database continued to grow if the compute time will also get longer?

What would be better to replace an expression in a query and is Access 2013 better to deal with complex and long expressions?