IIf statement with Or not working

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?

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.

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

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.

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")));

Hi there - need some help and mbizup has gone - have upped the point value to try and get some interest!

Louise

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")

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]))

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

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

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;

Select allOpen in new window

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

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?

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

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.

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;

Select allOpen in new window

Jparul I am cooking dinner at the moment - pls do not give up on me - determined to get this right

Jparul,
How do I get db to you - do not want to have viewed publicly, Louise

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?

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!)

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

Thanks so much you are a star!

Thank you so much for the comments and the points. Feel free to contact me if you have any more questions.