Combo box result and iif statement

[mcmuppet]

Hello

I have a combo box as a value list and all the values apart from one are valid data entries in the table but I want to combine two of the values and then get the query to run if the combined values are selected. The value list is this:


(All);Brand1;Brand2;Brand1 & Brand2;Brand3;Brand4

The criteria in my query is this:

IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![FrmCustomListATM]![cbBrand]))


Its the OR part that doesnt work, does anyone have any suggestions?

Many thanks

 

[CJ_London]

can you provide your entire query or at least the full criteria (everything from WHERE onwards)

 

[mcmuppet]

Hi

Here is the full SQL:


SELECT MyTable.Brand, MyTable.ID
FROM MyTable
WHERE (((MyTable.Brand)=IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![MyForm]![Brand])))) AND ((MyTable.Live)='Yes'));

 

[ritco]

I have a similar problem. I also have a combo box that has only three values, Branch 01, Branch02, or BothBranches.

WHERE ((dbo_SalesHisItemPcVw.Branch)=IIf([Forms]![frmRollingDates]![cboBranch]="01","01",IIf([Forms]![frmRollingDates]![cboBranch]="05","05","01" Or "05"))
If the user wants both branches combined, I can't figure out how to pass both values in the query. Is it even possible to return mulitple values through the Access IIf() function in a query?
I thought about creating two queries and setting the Report's DataSource property based on what is selected in the Branch combo box but that seems like the long way around and more objects to keep track of.

 

[CJ_London]

Try this solution

SELECT MyTable.Brand, MyTable.Id
FROM MyTable
WHERE (((MyTable.Brand)=IIf([Forms]![MyForm]![brand]='(Any)',[brand],IIf(InStr([Forms]![MyForm]![brand],' & ')=0,[Forms]![MyForm]![brand],False))) AND ((MyTable.Live)=True)) OR (((MyTable.Brand)=Left([Forms]![MyForm]![brand],InStr([Forms]![MyForm]![brand],[COLOR=red]' or '[/COLOR]))) AND ((MyTable.Live)=True)) OR (((MyTable.Brand)=Mid([Forms]![MyForm]![brand],InStr([Forms]![MyForm]![brand],[COLOR=red]' or '[/COLOR])+4)) AND ((MyTable.Live)=True));

Note: the assumption is that brand is text and that where you have combined two brands in your combobox it is combined with ' or ' (i.e. Ford or Chrysler) since a brand cannot be Ford and Chrysler. However if you want to change it, I have highlighted in red where you will need to change the sql. I recommend you do not use ampersands or slashes.

I've used the same names as you have so you should be able to post this into a query and see it in the designer. It will allow you to have more than one combining if required in the future, but would need rewriting if you decided to have three brands on a line in the combobox.

Because of the combining line I've also assumed your combo box is a value list and not a lookup from another table - if it is a lookup, you will need to look at a table and query redesign anyway

Gook luck!

 

[CJ_London]

Ritco - you really ought to post this to a new thread since, although similar it has a simpler solution.

The solution is a cut down version of what I posted last with some bits taken out

SELECT MyTable.Brand, MyTable.Id
FROM MyTable
WHERE (((MyTable.Brand)=IIf([Forms]![MyForm]![brand]='(Any)',[brand],IIf(InStr([Forms]![MyForm]![brand],' & ')=0,[Forms]![MyForm]![brand],False))) AND ((MyTable.Live)=True))

 

[mcmuppet]

CJ,

That worked perfectly and I would have never got to that answer!

Many thanks for your help

 

[ritco]

Sorry. Thought it would be easier in one thread. Will repost. Sorry for the inconvenience.

 

[CJ_London]

no problem

 

[ritco]

Thank you for your help. I was able to simplify the solution you posted a little more and got it working.

This is what ended up working for me:

Where ((dbo_SalesHisItemPcVw.Branch)=
IIf([Forms]![frmRollingDates]![cboBranch]="01","01",IIf([Forms]![frmRollingDates]![cboBranch]="05","05",[dbo_SalesHisItemPcVw]![Branch]))