Maximum 1024 Characters in a query

I tried to do an iif statement containing IIF(([Q1])Like "1", all the way to Q50 (having 50 questions) and was doing a Count function to count where all of these were a 1 and the query keeps stating that it is too long max 1024 characters and will be tuncated. Any ideas on how to write this in VB or another way around this?

re: Maximum 1024 Characters in a query

More questions than answers for you Supermansteel. Firstly, are you trying to determine whether the answers to all 50 questions are the same? Secondly, is each answer represented by a number, or by a string (text) value? Thirdly, is the value 1 the only possible answer other than 0, say?

You are using the 'like' operator intended for text values, but it is intended for partial matches using 'like "*somechars*"' syntax. Your use of 'like' is just the same as using '=', but takes far more characters.

If what you want to do is some form of brute-force check that all 50 answers are set to the value 1 (where 1 is the only value possible other than 0), returning a 1 if so and a 0 otherwise, then the shortest form I can think of would depend on those values being numeric, not text strings. You would have to include all the field names in your statement; I have abbreviated mine for clarity.

IIF([Q1]+[Q2]+[Q3] ... [Q49]+[Q50] = 50, 1, 0)

If your values are text and not numbers you would need to use

IIF(([Q1]="1") AND ([Q2]="1") AND ([Q3]="1") ... AND ([Q49]="1") AND ([Q50]="1"), 1, 0)

If your values are numbers but they can also take other values than 1 you need to use another ANDed version:

IIF(([Q1]=1) AND ([Q2]=1) AND ([Q3]=1) ... AND ([Q49]=1) AND ([Q50]=1), 1, 0)

There are simpler ways to accomplish the calculation in VBA, but rather than go into these at present (as calculating such a value is one thing, doing something useful with it another) you really need to assist us by being a bit clearer in what you are asking of us. I await your next post with interest.

-Stewart

re: Maximum 1024 Characters in a query

I will back track....My table has 150 fields. I don't know if this is the best way of doing what i did, but here it goes. Most of the fields in my table are state, branch, account#,and more account information. Then at the end of my table, I have 50 number fields, one for every question that i labeled Q1 thru Q50. These essentially are put are my Form as an Option Group as Yes=1, No=2 to each of the 50 Questions. Every time someone would click Yes it would be listed as an exception to Policy and was trying to create a count function to count how many exceptions during the month. (Listed below). This code below is actually from a second table I have 250 fields and 99 Questions and this is the one that keeps giving me an error. but there are suppose to be 99 questions and won't let me add anymore to the query.
I don't know if creating the table with all of these fields to store the questions is the most efficient way of getting this done, however, I have thought about seperating these two things and have a table to store all of the account information and have a Questions table to house all of the Questions and have a Main table to pull them together, but wasn't fully sure how to do it that way and if that way would be the most efficient and easiest to modify down the road.


Any input would be greatly appreciated. Please let me know if you do not understand anything that i have stated.

Thanks,

Matt

re: Maximum 1024 Characters in a query

Sorry, but your table design is not just inefficient. Inclusion of 50 questions breaches first normal form rules about separation of repeating groups into their own tables. Relational tables rarely have more than a few tens of fields at most - so 150 fields before your questions were added says straight away that your table design is not normalised. I guess it looks more like an Excel free-form table, containing many entities which should be split out into their own tables.

You can't work on unnormalised data with a relational database and get anything like the flexibility and performance you could with normalised data. You are also throwing away the power of the database engine, which is designed to optimise table performance where one-to-many relations have been designed and enforced from the start.

As general advice on table design please see the following HowTo article on Database Normalisation and Table Structures.

In terms of the question problem you should have at least a separate question table, and a response table linking account and question. To add to your plight your individual response fields should simply have been boolean (yes/no) values, not numerics taking the value 1 and 2.

I have corrected the where clause below as far as I can, but your design problems have already taken you past Access's 255 field limits (the reason you cannot add more questions to your table).

You need to redesign your tables from scratch. As it is your table design I am sorry to say is not just inefficient it is completely unmaintainable, and there is no point in trying further workarounds...

-Stewart

re: Maximum 1024 Characters in a query

Thanks for the advice. I will have to go back to the drawing board on this. I am usually alot better with table designing, I don't know why I was trying to do this the hard way.

Matt