IIf statement with Or not working

I am trying to write a very simple IIf statemnt (for the first time!) in a query.  I have tested it to make sure the "or" part of the query works without the IIf statement and it comes back with the correct data.  However when I put it in the IIf statement it doesn't work.  If I have one condition and don't use or it works.  Please help.

IIf([Forms]![frmMarketingAnalysis]![optCurrentCust]=True,"New Customer" Or "Existing Customer")

Thanks
luigi_pregoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
The syntax of iif should be:

IIf({some condition}, {result if that condition is True}, {result if that condition is false})

What are you trying to do with the OR?
0
mbizupCommented:
Maybe this?

IIf([Forms]![frmMarketingAnalysis]![optCurrentCust]=True,"New Customer","Existing Customer")

Using this statement, the result will be "New Customer" if optCurrentCustomer is true and "Existing Customer" if optCurrentCustomer is false.
0
luigi_pregoAuthor Commented:
Daer mbizup - thanks for swift response

Trying to use Or to limit records to new customer or existing customer - if false can show all records.

Louise
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
So you need to
- Determine if Forms]![frmMarketingAnalysis]![optCurrentCust]=True
- If yes show only records where  {some field} is "New Customer" Or "Existing Customer")
- If no, show all records

Is that correct?

Please also post the current SQL view of your query.

I have to run out to a meeting, but this info will help other Experts.  I will check in later this afternoon to see how this is going.
0
luigi_pregoAuthor Commented:
Yes that is correct
SQL:

SELECT Count(Customers.CustomerNumber) AS CountOfCustomerNumber, tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
FROM (tblMarketing INNER JOIN Customers ON tblMarketing.MarketingID = Customers.MarketingID) INNER JOIN Children ON Customers.CustomerNumber = Children.CustomerNo
GROUP BY tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
HAVING (((Customers.Datefirstcontacted) Between ([Forms]![frmMarketingAnalysis]![txtStartDate]) And ([Forms]![frmMarketingAnalysis]![txtEndDate])) AND ((Children.Status)=IIf([Forms]![frmMarketingAnalysis]![optCurrentCust]=True,(Children.Status)="New Customer" Or (Children.Status)="Existing Customer")));
0
luigi_pregoAuthor Commented:
Hi there - need some help and mbizup has gone - have upped the point value to try and get some interest!

Louise
0
FPLESCommented:
are you using the IIF statement as a condition or a field??  What you can do is put it as a field, as in
IIF(Forms]![frmMarketingAnalysis]![optCurrentCust] = True , "Current Customer"," Not Current customer")
0
FPLESCommented:
Here is another example using your query

SELECT Count(Customers.CustomerNumber) AS CountOfCustomerNumber, tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status, IIF(Forms]![frmMarketingAnalysis]![optCurrentCust] = True , "Current Customer"," Not Current customer") as CustStatus
FROM (tblMarketing INNER JOIN Customers ON tblMarketing.MarketingID = Customers.MarketingID) INNER JOIN Children ON Customers.CustomerNumber = Children.CustomerNo
GROUP BY tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
HAVING (((Customers.Datefirstcontacted) Between ([Forms]![frmMarketingAnalysis]![txtStartDate]) And ([Forms]![frmMarketingAnalysis]![txtEndDate]))
0
luigi_pregoAuthor Commented:
Don't really understand what you are getting at.  I have an option button on a form which allows user to limit records just to customers with status of new or existing - what I am trying to do is then pass this to the query which generates the report.  If they click on option button (true) collects data on just new and existing custs, if they don't making it false I want user to see all records - all the rest of the customers whatever their status.  My problem comes in combining the new and existing customers in the iif statememt.  I can't write SQL yet! Louise
0
FPLESCommented:
What is the Status field name in your table?  because you can group it with an IIF statement ,  for example =>  IIF(Cust_Status = "New Customer" or Cust_Status = "Existing Customer", "Status","NoStatus")  and base the criteria on the option button,  IIF(Forms]![frmMarketingAnalysis]![optCurrentCust] = True , "Status","No Status")

Cust_status being your table field name


0
jparulCommented:
In this situation, I would use a CASE WHEN instead of IIF statement.
Something like:
 

SELECT Count(Customers.CustomerNumber) AS CountOfCustomerNumber, tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
FROM 
	(tblMarketing INNER JOIN Customers ON tblMarketing.MarketingID = Customers.MarketingID) 
	INNER JOIN Children ON Customers.CustomerNumber = Children.CustomerNo
 
	GROUP BY tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
	HAVING (((Customers.Datefirstcontacted) Between ([Forms]![frmMarketingAnalysis]![txtStartDate]) And ([Forms]![frmMarketingAnalysis]![txtEndDate])) AND 
	case 
	     when 
		[Forms]![frmMarketingAnalysis]![optCurrentCust]=True
	     then
		(Children.Status)="New Customer" Or (Children.Status)="Existing Customer")
	end;

Open in new window

0
luigi_pregoAuthor Commented:
Have done what you suggest but only seem to get back data with "Status" - whether the option button is true or false.  I put the first part of your statement in a field in the query and the second part in the criteria - the field name is [Children]![Status]  it has automatically put "expression in the total row.  The query also automatically created an extra column with the first part of the statement in the field with "group by" in the Total row - it will not work without this column, I have tried - Not sure what is going on!
Louise
0
jparulCommented:
I am not so familiar with the Query designer view. Can you try to paste the above query in the SQL view and then try to run it?
0
luigi_pregoAuthor Commented:
jparul

Have pasted query in SQL view and tried to run - coming back with syntax error missing operator in query expression from '(((Customers.Datefirstcontacted .....(Children.Status)="NewCustomer" Or (Chi'.

I feel very lost!

Louise
0
jparulCommented:
Can you attach a sample of your Access DB? In the meanwhile I am working on the query, trying to figure where the error is.
0
jparulCommented:
I tried to remove parenthesis(there was an extra one)
Try it now and see if it works, else I'll wait for your DB
 

SELECT Count(Customers.CustomerNumber) AS CountOfCustomerNumber, tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
FROM 
	tblMarketing INNER JOIN Customers ON tblMarketing.MarketingID = Customers.MarketingID 
	INNER JOIN Children ON Customers.CustomerNumber = Children.CustomerNo
 
	GROUP BY tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
	HAVING 
	Customers.Datefirstcontacted Between [Forms]![frmMarketingAnalysis]![txtStartDate] And [Forms]![frmMarketingAnalysis]![txtEndDate]
	AND 
	case 
	     when 
		[Forms]![frmMarketingAnalysis]![optCurrentCust]=True
	     then
		(Children.Status)="New Customer" Or (Children.Status)="Existing Customer"
	end;

Open in new window

0
luigi_pregoAuthor Commented:
Jparul I am cooking dinner at the moment - pls do not give up on me - determined to get this right
0
luigi_pregoAuthor Commented:
Jparul,
How do I get db to you - do not want to have viewed publicly, Louise

0
jparulCommented:
Yes, if you upload it here, it can be viewed publicly. Before that, did you try the changed query I posted after removing the parenthesis?
0
luigi_pregoAuthor Commented:
Hi jparul, I'm afraid got another syntax error (missing operator)...'tblMarketing.MarketingID = Customers.MarketingID INNER JOIN Children ON Customers.CustomerNumber  = Children.CustomerNo'.

I don't mind sending some code but what do you want to see - not sure any of it will help - or do you want to see the whole thing?

Louise (bear with me - running from kitchen to computer in office at end of garden!)
0
jparulCommented:
Try this. I tested it at my end
SELECT Count(Customers.CustomerNumber) AS CountOfCustomerNumber, tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
FROM 
(	tblMarketing INNER JOIN Customers ON tblMarketing.MarketingID = Customers.MarketingID)
	INNER JOIN Children ON Customers.CustomerNumber = Children.CustomerNo
 
	GROUP BY tblMarketing.Referral, Customers.Datefirstcontacted, Children.Status
	HAVING 
	Customers.Datefirstcontacted Between [Forms]![frmMarketingAnalysis]![txtStartDate] And [Forms]![frmMarketingAnalysis]![txtEndDate]
	AND 
	(([Forms]![frmMarketingAnalysis]![optCurrentCust]=True and 
	(Children.Status='New Customer' Or Children.Status='Existing Customer')) 
	OR ([Forms]![frmMarketingAnalysis]![optCurrentCust]=False));

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
luigi_pregoAuthor Commented:
Dear jparul, thank you so much - it worked.  I am going to have a long look at your code and try to get to grips with what you did so that I can properly understand it.  This is a much neater solution than having to create 2 queries and 2 reports just for a simple difference - which is what I wanted to achieve.  I have upped the points due to your sticking with me through dinner etc.

Louise
0
luigi_pregoAuthor Commented:
Thanks so much you are a star!
0
jparulCommented:
Thank you so much for the comments and the points. Feel free to contact me if you have any more questions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.