acarterczyz
Registered User.
- Local time
- Today, 14:07
- Joined
- Apr 11, 2013
- Messages
- 68
This didn't work. It returns all records regardless if checkbox is checked or not.
Query Criteria with IFF statement and Checkbox (1 Viewer)
- Thread starter acarterczyz
- Start date
This didn't work. It returns all records regardless if checkbox is checked or not.
HiTechCoach
Well-known member
- Local time
- Today, 14:07
- Joined
- Mar 6, 2006
- Messages
- 4,357
acarterczyz, were you able to view my example database?
acarterczyz
Registered User.
- Local time
- Today, 14:07
- Joined
- Apr 11, 2013
- Messages
- 68
Yes I did. When I open design view on your query, it opens the SQL. When I open the design view on my query, I get the builder page. I know nothing about SQL/Coding.. and can barely get by with the formulas I'm putting in (until this one). I copied/pasted and edited in the SQL from your sample and it came up with a Syntax Error.
Here is my SQL code:
Code:
SELECT Detail.*, *
FROM Detail
WHERE (((Detail.Primary)=IIf([Forms]![AdvancedReporting]![Combo216]="ALL","*",[Forms]![AdvancedReporting]![Combo216])) AND ((Detail.[Asgn Type]) Like IIf([Forms]![AdvancedReporting]![Check226]=False,IIf([Forms]![AdvancedReporting]![Check224]=False,IIf([Forms]![AdvancedReporting]![Check222]=False,"*",'NPP'),'SG'),'YB')) AND (IIf((Detail.[E-Mail])=[Forms]![AdvancedReporting]![Check230] And Not (Detail.[E-Mail])=IsNull([Detail].[E-Mail]),True,False)=[Forms]![AdvancedReporting]![Check230]));HiTechCoach
Well-known member
- Local time
- Today, 14:07
- Joined
- Mar 6, 2006
- Messages
- 4,357
I edited the original SQL you posted to be:
Code:
SELECT Detail.*, *
FROM Detail
WHERE (
(
(Detail.Primary)=IIf([Forms]![AdvancedReporting]![Combo216]="ALL","*",[Forms]![AdvancedReporting]![Combo216])
)
AND (
(Detail.[Asgn Type])=
IIf([Forms]![AdvancedReporting]![Check226]=False,
IIf([Forms]![AdvancedReporting]![Check224]=False,
IIf([Forms]![AdvancedReporting]![Check222]=False,"*",'NPP')
,'SG')
,'YB')
)
AND (
IIF( [Forms]![AdvancedReporting]![Check230] and not IsNull(Detail.[E-Mail]),True,False) = [Forms]![AdvancedReporting]![Check230])
);acarterczyz
Registered User.
- Local time
- Today, 14:07
- Joined
- Apr 11, 2013
- Messages
- 68
Thank you HiTechCoach. A co-worker got it to work, but now we have run into the issue where we have too much information in the table and Access will not work.
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
The database worked perfectly when we had just a little bit in the Details table. But when I added almost 2 million records, it was too much for it to handle. Not to mention, that 2 million records isn't nearly half of what I need to put in there.
Any suggestions of a different program that would allow to do this?
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
The database worked perfectly when we had just a little bit in the Details table. But when I added almost 2 million records, it was too much for it to handle. Not to mention, that 2 million records isn't nearly half of what I need to put in there.
Any suggestions of a different program that would allow to do this?
Code:
SELECT Detail.House, Detail.Street, Detail.Town, Detail.State, Detail.Zip, Detail.Name, Detail.[Lead#], IIf(IsNull([Detail]![Asgn Type])=False,[Detail]![Asgn Type],"No Data") AS [Asgn Type], Detail.[Competitor$], Detail.Revenue, Detail.[Lead Rep], Detail.Phone, Detail.[Heading#], Detail.[Heading Description], IIf([Forms]![AdvancedReporting]![Combo216]<>"All Records",IIf([Detail]![Primary]=[Forms]![AdvancedReporting]![Combo216],[Detail]![Primary],Null),[Detail]![Primary]) AS [Primary], Detail.[Rep Code], Detail.[Rep Name], Detail.[Zone Code], Detail.[Zone Description], IIf([Forms]![AdvancedReporting]![Check230]=0,IIf(IsNull([Detail]![E-Mail])=False,[Detail]![E-Mail],"No Data"),[Detail]![E-Mail]) AS [E-Mail], IIf([Forms]![AdvancedReporting]![Check232]=0,IIf(IsNull([Detail]![URL])=False,[Detail]![URL],"No Data"),[Detail]![URL]) AS URL, IIf([Forms]![AdvancedReporting]![Check234]=0,IIf(IsNull([Detail]![Annual sales])=False,[Detail]![Annual sales],"No Data"),[Detail]![Annual sales]) AS [Annual Sales], IIf([Forms]![AdvancedReporting]![Check236]=0,IIf(IsNull([Detail]![# Employees]),"No Data",[Detail]![# Employees]),[Detail]![# Employees]) AS [# Employees], IIf([Forms]![AdvancedReporting]![Check238]=0,IIf(IsNull([Detail]![Year Started]),"No Data",[Detail]![Year Started]),[Detail]![Year Started]) AS [Year Started], Detail.[Line of Business], IIf([Forms]![AdvancedReporting]![Check240]=0,IIf(IsNull([Detail]![Contact Name])=False,[Detail]![Contact Name],"No Data"),[Detail]![Contact Name]) AS [Contact Name], Detail.[Contact Title], IIf([Forms]![AdvancedReporting]![Check244]=0,IIf(IsNull([Detail]![FRANCHISE])=False,[Detail]![FRANCHISE],"No Data"),[Detail]![FRANCHISE]) AS Franchise, Detail.[FRANCHISE OWNERSHIP], Detail.[PHYSICAL LOCATION OF FRANCHISE], IIf([Forms]![AdvancedReporting]![Check242]=0,IIf(IsNull([Detail]![MODELED MARKETING SPEND])=False,[Detail]![MODELED MARKETING SPEND],"No Data"),[Detail]![MODELED MARKETING SPEND]) AS [MODELED MARKETING SPEND], IIf([Forms]![AdvancedReporting]![Check246]=0,IIf(IsNull([Detail]![BRANDS])=False,[Detail]![BRANDS],"No Data"),[Detail]![BRANDS]) AS BRANDS, IIf(IsNull([Detail]![SEO Score]),0,FormatNumber([Detail]![SEO Score])) AS [SEO Score], Detail.[SEO Secure Socket Layer], Detail.[SEO Title], Detail.[SEO Metadata], Detail.[SEO Site Map], Detail.[SEO Shopping Cart]
FROM Detail
WHERE (((IIf(IsNull([Detail]![Asgn Type])=False,[Detail]![Asgn Type],"No Data")) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Combo216]<>"All Records",IIf([Detail]![Primary]=[Forms]![AdvancedReporting]![Combo216],[Detail]![Primary],Null),[Detail]![Primary])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check230]=0,IIf(IsNull([Detail]![E-Mail])=False,[Detail]![E-Mail],"No Data"),[Detail]![E-Mail])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check232]=0,IIf(IsNull([Detail]![URL])=False,[Detail]![URL],"No Data"),[Detail]![URL])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check234]=0,IIf(IsNull([Detail]![Annual sales])=False,[Detail]![Annual sales],"No Data"),[Detail]![Annual sales])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check236]=0,IIf(IsNull([Detail]![# Employees]),"No Data",[Detail]![# Employees]),[Detail]![# Employees])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check238]=0,IIf(IsNull([Detail]![Year Started]),"No Data",[Detail]![Year Started]),[Detail]![Year Started])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check240]=0,IIf(IsNull([Detail]![Contact Name])=False,[Detail]![Contact Name],"No Data"),[Detail]![Contact Name])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check244]=0,IIf(IsNull([Detail]![FRANCHISE])=False,[Detail]![FRANCHISE],"No Data"),[Detail]![FRANCHISE])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check242]=0,IIf(IsNull([Detail]![MODELED MARKETING SPEND])=False,[Detail]![MODELED MARKETING SPEND],"No Data"),[Detail]![MODELED MARKETING SPEND])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check246]=0,IIf(IsNull([Detail]![BRANDS])=False,[Detail]![BRANDS],"No Data"),[Detail]![BRANDS])) Is Not Null) AND ((IIf(IsNull([Detail]![SEO Score]),0,FormatNumber([Detail]![SEO Score]))) Between [Forms]![AdvancedReporting]![Combo255] And ([Forms]![AdvancedReporting]![Combo258]+1)) AND ((IIf([Forms]![AdvancedReporting]![Check222]=0,Null,IIf([Detail]![Asgn Type]="NPP",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check224]=0,Null,IIf([Detail]![Asgn Type]="SG",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check226]=0,Null,IIf([Detail]![Asgn Type]="YB",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check222]=-1 Or [Forms]![AdvancedReporting]![Check224]=-1 Or [Forms]![AdvancedReporting]![Check226]=-1,Null,IIf(IsNull([Detail]![Asgn Type]),"No Data",[Detail]![Asgn Type]))) Is Not Null));- Local time
- Today, 20:07
- Joined
- Feb 19, 2013
- Messages
- 16,612
Before deciding to move to another platform I would review your query and all of your data - if there is a problem with the data or query you will still get the same problem on another platform.
The number of records is not the issue - the error message is a bit misleading and actually implies the query has come across a situation that it can't cope with which typically means something in the data - perhaps a null where none is expected or a number out of range (expecting integer and getting long) or a divide by zero. Alternatively, if you are using nested queries (i.e. query A is based on Query B) and there is a problem in query B or you are referencing a form that isn't open or is open but the control referenced has not been properly completed (eg. has a null value).
Something else to consider is to abbreviate your form and control names (if they are too long it can cause problems although yours look as if they are OK) or better still, have VBA build the query so you are not using a reference to the form but provide the actual value.
However, from past experience, my bet is that you have an issue with your data or form
Ways to review data:
Sort each column in turn in your table(s) A-Z and inspect the first and last few records - if you have 2 million+ that might mean the first/last few thousand although you will probably find it in the top 10 or 20 records.
The number of records is not the issue - the error message is a bit misleading and actually implies the query has come across a situation that it can't cope with which typically means something in the data - perhaps a null where none is expected or a number out of range (expecting integer and getting long) or a divide by zero. Alternatively, if you are using nested queries (i.e. query A is based on Query B) and there is a problem in query B or you are referencing a form that isn't open or is open but the control referenced has not been properly completed (eg. has a null value).
Something else to consider is to abbreviate your form and control names (if they are too long it can cause problems although yours look as if they are OK) or better still, have VBA build the query so you are not using a reference to the form but provide the actual value.
However, from past experience, my bet is that you have an issue with your data or form
Ways to review data:
Sort each column in turn in your table(s) A-Z and inspect the first and last few records - if you have 2 million+ that might mean the first/last few thousand although you will probably find it in the top 10 or 20 records.
If this is true, another platform will be your worst nightmare - Access probably has the friendliest user interface going
HiTechCoach
Well-known member
- Local time
- Today, 14:07
- Joined
- Mar 6, 2006
- Messages
- 4,357
You are correct. Good catch. I never look close at the select clause. The second * needs remove.
Example:
Code:
SELECT Detail.*FROM Detail
WHERE ( ...HiTechCoach
Well-known member
- Local time
- Today, 14:07
- Joined
- Mar 6, 2006
- Messages
- 4,357
Oh my .... I tremble at the thought of trying to maintain your query.
I agree with CJ_London that you need to review your query and methods. Using an SQL Server as the backend may help with the database size (number of records) but that it will not help if you do not create a properly formatted queries. I totally agree with CJ_London on " another platform will be your worst nightmare - Access probably has the friendliest user interface going."
Access's SQL language is very forgiving and als allows the use of form references ( like [Forms]![AdvancedReporting]![Check226]) and VBA functions. Id you were toi switch to an SQL server you would have to learn a totally new way.
I agree that you need to check your data. Bad data will cause lots of issues with IIF functions. What would be better is to eliminate the use of all the IIF() function calls. You really should be building the SQL dynamically.
Hi, HiTechCoach!!!
I downloaded your DB and your code works like a charm for a single check box.
How would you do it for multiple checkboxes?
