IIf Criteria from a Text Box

[GordonB]

Hi folks.

Please, can someone put me out of my misery?

On a form, I have a textbox. This value is used as criteria in a select query.

I need it to do this:

IIf([Forms]![Report Form]![Text268]="Company Name", then use "Company Name" as the criteria, but if the text box has "Other Companies" then use everything except "Company Name" as the criteria.

Many thanks in advance.

Gordon.

 

[mkelly]

does "other company" mean any other company name in your code?

 

[GordonB]

Hi mkelly.

Yes. Company Name is the main company who contract out to several other companies.

Think of the field name [Fruit]. In the criteria box for this field there will be two choices that come from the text box. One choice, for example" would be "apple" and the other choice would be "other fruit", which should return all types of fruit except apple.

Does this make sense?

Gordon.

 

[Mile-O]

Lookup cascading comboboxes - sounds like what you want.

 

[Jon K]

You need to put an IIF expression in the Field row and put <>False in the Criteria row. See the query in the attached database.

In the database, I used a combo box on the form. I think it is easier to control user input with a combo box than a text box.

 

[GordonB]

Mile-O-Phile,

Thank you for your response. However, I can't think how cascading comboboxes would've helped; I wasn't trying to select another particular company from the list, I wanted them all except one.

Can you enlighten me a bit more, please?


Jon K,

That's perfect for my needs. I would never have thought of making another field; I was trying all sorts in the criteria box in the Company field. You're absolutely right about using a combobox to select. I'll do that.

Many thanks

Gordon.