Solved

Query only takes a little over 1000 characters

Posted on 2013-06-29
15
284 Views
Last Modified: 2013-07-08
I have a form that uses text boxes with code and a query in the form.

This was easier for me since Im not good at making complex calculations in query's.

The problem I had before was using the IIF statment with Between AND. Which I thank all the expert for there help and time.

Now I have come into a delima.

My query is over 1000 characters!! It was suggested that I use a Function. Which Im not familar with. And dont know how to incorparate it with how I have the form done now.

Help experts!

Please talk to me as simple as you can so that I can get it. Enclosed is the form that has the query in the form that calculates, and the summary with text boxes that have calculations in it also.

Again thanks for time and help!!!!!
HelpWithQuery--2-.zip
0
Comment
Question by:gigifarrow
  • 7
  • 6
  • 2
15 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39287056
Is it working for you?
I don't think 1000 characters or even 2000 is going to hinder you, except that it makes it harder to make changes, find errors in typing the SQL.
It looks like you went away from the BETWEEN/AND statement in the query, and are expecting a range to be determined using the '-'.
So,  IIf([Production]=343-346
evaluates to Iff(production = -3
and so on.
Am i reading this correctly?
0
 

Author Comment

by:gigifarrow
ID: 39287670
No I changed it to Between and just hadnt changed it here becsuse using - doesnt work
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39287678
Well, need to see what you are using.
Also, does Between/And work? If not, what happens?
What do you want?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:gigifarrow
ID: 39287686
I need to add another iif statement because there are three different criterias but it goes over 1000 . So I  dont know what to do now
0
 

Author Comment

by:gigifarrow
ID: 39287688
I have text with code. In the form using all queries dont know how to dobanything else
0
 
LVL 6

Assisted Solution

by:limweizhong
limweizhong earned 250 total points
ID: 39288057
Non-intrusive:
1. Remove all the spaces after the commas.
2. Remove double spaces (I see one).
3. Remove all "F." and "[F]!", and "AS F"
4. Remove all unnecessary square brackets like "[Induction]".
5. Remove the extra "(" and ")" around the "Abs" calls.

Intrusive:
1. Remove "+Provision*0" and the like, since they contribute nothing.
2. Use the distributive rule of multiplication over addition to factor out the multiple "*10"s.
3. Replace "/100)*100" with ")" because "Abs" just removes the sign. Except that I think "Abs" is supposed to be "Int".
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39288701
OK, I see now. 1024 characters for a query field definition is the limit.
Barring some previously offered suggestions (function, table of [Product] values), I think the BETWEEN/AND conditions might be separated from the calculations, in a true/false field. Although it might be better to make the separate table of products which get certain calculations, since those products might change over time. I'll look.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39288710
off topic:  Remember NIE in 2011?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39288721
back on topic:
Couple of questions: Your current formula provides only 2 conditions, [product] number is a set list of values, or it is not. Then a different formula is provided for each condition. Will there be other possible percentage formulas for other [product] values?
Can you add a field or so to the root table, indicating which formula is to be used?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39288771
Correction: [Production] vs [Product]
If you add this to your query in design view-
calc1:([f].[production] Between 343 And 536) And [f].[production] Not In (375,376,377,378,381,382,383,384,385,386,387,388,389,390,404,408,411,423,429,430,431,465,466,468,469,470,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523)
and edit [Percentages] to read-
Percentages: IIf([calc1],(Abs(([F]![Induction]*6+[F]![TearDown]*10+[F]![Provision]*10+[F]![AssemblyA]*10+[F]![AsemblyB]*10+[F]![AssemblyC]*10+[F]![AssemblyD]*10+[F]![AssemblyE]*10+[F]![RoadTest]*4+[F]![QC]*10+[F]![OutDuction]*10)/100)*100),(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100)*100))

You should be way under your character limit for a query cell definition.

However, classifying the calculation to be used for a [Production] number in the root table is a better idea.
0
 
LVL 6

Expert Comment

by:limweizhong
ID: 39288893
If it is a character limit for a query cell definition, I don't see why it even exceeds in the first place, even when you use the Between... And operator. After I converted it, I got 844 characters, and the query seems to give no error.

Percentages: IIf([Production] Between 343 And 346 Or [Production] Between 347 And 374 Or [Production] Between 379 And 380 Or [Production] Between 391 And 403 Or [Production] Between 405 And 407 Or [Production] Between 409 And 410 Or [Production] Between 412 And 422 Or [Production] Between 424 And 428 Or [Production] Between 432 And 464 Or [Production]=467 Or [Production] Between 471 And 507 Or [Production] Between 524 And 536,(Abs(([F]![Induction]*6+[F]![TearDown]*10+[F]![Provision]*10+[F]![AssemblyA]*10+[F]![AsemblyB]*10+[F]![AssemblyC]*10+[F]![AssemblyD]*10+[F]![AssemblyE]*10+[F]![RoadTest]*4+[F]![QC]*10+[F]![OutDuction]*10)/100)*100),(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100)*100))

Open in new window

0
 

Author Comment

by:gigifarrow
ID: 39290209
I hadnt put the third criteria on because ikept getting  error message. It will be anther field as sbove with dlight different numbers.


can I really tske out this "(F)"
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 250 total points
ID: 39290371
As long as you are only using one table in the query, yes, you may. As well as the alias 'as F'.
However, if you edit the query in design view, it will probably put the table name (or alias)  preceding the field name.
0
 

Author Comment

by:gigifarrow
ID: 39290474
Wow I will try that!  Thst might be the answer I I whant to thank all og the experts for your time I eill back as soon as I try thid
0
 

Author Comment

by:gigifarrow
ID: 39307506
Thank you for all the advice. I did take out the [F]![    ] . and then I put the extra criteria and it was still too  much. So I guess I need another alternative.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help in SQL 13 37
ADODB problem 20 37
SQL Select in Access 2003 3 25
Gracefully handling 'Record Locked'  Errors 33 34
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question