Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Course Of The Month
6 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Query only takes a little over 1000 characters
Posted on 2013-06-29
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
7-
6
2
15 Comments
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?
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?
Well, need to see what you are using.
Also, does Between/And work? If not, what happens?
What do you want?
Also, does Between/And work? If not, what happens?
What do you want?
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
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".
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".
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.
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.
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?
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?
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,3
and edit [Percentages] to read-
Percentages: IIf([calc1],(Abs(([F]![Ind
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.
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,3
and edit [Percentages] to read-
Percentages: IIf([calc1],(Abs(([F]![Ind
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.
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))
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)"
can I really tske out this "(F)"
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.
However, if you edit the query in design view, it will probably put the table name (or alias) preceding the field name.
Featured Post
![[Webinar] How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/20/3315d9d8-8110-4d52-be94-a5aacdd83e4b.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
Learn how to number pages in an Access report over each group.
Activate two pass printing by referencing the pages property:
Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses
Course of the Month6 days, 7 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.