Hi there,
I have a problem with a query in which I am trying to set the criteria from a list box. The table column being queried has 3 possible entries, M, R or C. The list box forces the user to select one of those three choices, but when the query is run i need the criteria to be set so that if the user chooses M, the query searches for M and R and C, if they choose R then it queries R and C, and if they select C then it just returns the C entries.
The method i was going to use was to have the entry

Like [Forms]![Price list request]![Option choice]

as my criteria, but i cannot filter by multiple object like that as the list box will not allow the proper search criteria of "M" or "R" or "C" to be entered as a free text choice in the column.

I keep thinking i have solved it and then i run into a syntax error.

Any assistance would be greatly appreciated.

Hi there,
I have a problem with a query in which I am trying to set the criteria from a list box. The table column being queried has 3 possible entries, M, R or C. The list box forces the user to select one of those three choices, but when the query is run i need the criteria to be set so that if the user chooses M, the query searches for M and R and C, if they choose R then it queries R and C, and if they select C then it just returns the C entries.
The method i was going to use was to have the entry

Like [Forms]![Price list request]![Option choice]

as my criteria, but i cannot filter by multiple object like that as the list box will not allow the proper search criteria of "M" or "R" or "C" to be entered as a free text choice in the column.

I keep thinking i have solved it and then i run into a syntax error.

Any assistance would be greatly appreciated.

I believe that something like the following will get you started:


IIf(Like [Forms]![Price list request]![Option choice]="C", "C",
IIf(Like [Forms]![Price list request]![Option choice]="R", "RC", "MRC))

You have indicated that the value this field contains will be in the set "MRC". If the possibility of any other value exists, then you will need to add one more IIf() at the end to handle that case.

IIf(Like [Forms]![Price list request]![Option choice]="C", "C",
IIf(Like [Forms]![Price list request]![Option choice]="R", "RC", "MRC))



Forgive me for being an utter noob but this won't go in the criteria box for the query as having too many characters.

Also i badly explained the problem, the column may have either an M or an R or a C in it, if the user chooses the M option i wish for all those three to be found, but no one field will have more than one of those three characters in it.

And thanks for your response.

I have attempted to use the following as my criteria, and I am told that i have a string that is too long. Is there some way of shortening this?

IIf(Like [Forms]![Price list request]![Option choice]="C", "C", IIf(Like [Forms]![Price list request]![Option choice]="R", "R", "C", IIf(Like [Forms]![Price list request]![Option choice]="M", "M", "R", "C)))

IIf(Like [Forms]![Price list request]![Option choice]="C", "C",
IIf(Like [Forms]![Price list request]![Option choice]="R", "RC", "MRC))



Forgive me for being an utter noob but this won't go in the criteria box for the query as having too many characters.

Also i badly explained the problem, the column may have either an M or an R or a C in it, if the user chooses the M option i wish for all those three to be found, but no one field will have more than one of those three characters in it.

And thanks for your response.

Looks like I made a VERY basic Cut/Paste error (I left the work Like in by mistake). The code should have been as follows:
[quote=Canicula;807913]
IIf([Forms]![Price list request]![Option choice]="C", "C",
IIf([Forms]![Price list request]![Option choice]="R", "RC", "MRC))

You can't simply refer to a multiselect listbox for the selected values. Presuming the query is for a form or report, you can use this technique:

http://www.baldyweb.com/multiselect.htm

Ok i have set this as my query in SQL and it is saying i have the wrong number of argument on the last line. I am obviously doing something wrong but I can't see any discrepencies and i have tried to set it as you did without the other parameters and still no joy.

SELECT [Part W/U relationships].[part no], [MINX Spare Parts Pricing].description, [Quantity]*[Quantity factor]*Forms![Price list request]![no of kits] AS [Kit Quantity], [MINX Spare Parts Pricing].[Standard cost], [MINX Spare Parts Pricing].
[list price], [Additional part info].[MVT-HR Notes], [MINX Spare Parts Pricing].[General part notes], [Part W/U relationships].[MVT-HR]
FROM (([Additional part info] INNER JOIN [Part number qty] ON [Additional part info].[part no] = [Part number qty].[part no]) INNER JOIN [Part W/U relationships] ON [Part number qty].[part no] = [Part W/U relationships].[part no]) INNER JOIN [MINX Spare Parts Pricing] ON [Part W/U relationships].[part no] = IIf([Forms]![Price list request]![Option choice]="C", "C", IIf([Forms]![Price list request]![Option choice]="R", "R", "C", IIf([Forms]![Price list request]![Option choice]="R", "M", "R", "C")));

Ok i have set this as my query in SQL and it is saying i have the wrong number of argument on the last line. I am obviously doing something wrong but I can't see any discrepencies and i have tried to set it as you did without the other parameters and still no joy.

SELECT [Part W/U relationships].[part no], [MINX Spare Parts Pricing].description, [Quantity]*[Quantity factor]*Forms![Price list request]![no of kits] AS [Kit Quantity], [MINX Spare Parts Pricing].[Standard cost], [MINX Spare Parts Pricing].[list price], [Additional part info].[MVT-HR Notes], [MINX Spare Parts Pricing].[General part notes], [Part W/U relationships].[MVT-HR]
FROM (([Additional part info] INNER JOIN [Part number qty] ON [Additional part info].[part no] = [Part number qty].[part no]) INNER JOIN [Part W/U relationships] ON [Part number qty].[part no] = [Part W/U relationships].[part no]) INNER JOIN [MINX Spare Parts Pricing] ON [Part W/U relationships].[part no] = IIf([Forms]![Price list request]![Option choice]="C", "C", IIf([Forms]![Price list request]![Option choice]="R", "R", "C", IIf([Forms]![Price list request]![Option choice]="R", "M", "R", "C")));

Your Compound IIf() Statement at the end is formatted incorrectly. Some of them have more than the required three parts. Not being exactly sure of your intent, I cannot suggest a modification, so I will describe the proper format and perhaps you can redo it.

A properly formatted IIf() Statement has three parts:

IIf(
{ Condition to test },
{ Result or Action If TRUE },
{ Result or Action if FALSE }
)

I shall have to work on this from the beginning i guess and see where i went wrong. Thanks to you both for the help, it's very much appreciated.