IF statement in SELECT query?

[Henley12]

I have an odd situation. I am trying to program a combo box to select two different sets of criteria depending on the value in a text box on the form. For example, when I open up the form, it is pulling in a Manufacturer name from another form based on the Equipment ID. But if the equipment id is 'miscellaneous', the newly opened form field will have 'misc' in the text box. If the text box has 'misc', I want the combo box to display all choices from the underlying query. If the text box has the name of a manufacturer, I want it to only display those records in the combo box. I hope this makes sense.

 

[JPearson]

Sounds more like you should use VBA to change and refresh the query of that cbo box.

 

[vbaInet]

Sounds more like you should use VBA to change and refresh the query of that cbo box.

Combo1.Requery

You would need this too:

http://baldyweb.com/wherecondition.htm

 

[CraigDolphin]

or use a criteria expression along the lines of:

Like IIF(Forms!Formname!textboxname = 'misc','*',Forms!Formname!textboxname)

You should also trap for nulls in case the value in the textbox is empty

 

[Henley12]

or use a criteria expression along the lines of:

Like IIF(Forms!Formname!textboxname = 'misc','*',Forms!Formname!textboxname)

You should also trap for nulls in case the value in the textbox is empty

That worked like a charm. Anyone else have those days when you just feel like an idiot? Thanks for the help.

 

[CraigDolphin]

Anyone else have those days when you just feel like an idiot?

Only on days ending with 'day'

Glad it worked for you.

 

[vbaInet]

Only on days ending with 'day'

Glad it worked for you.

Even on SunDAY and SaturDAY? Pretty much everyDAY then? hehe!

 

[CraigDolphin]

I'm married. There is no respite from ego deflation even on weekends

 

[vbaInet]

Short breaks away (once in a while) would help. Then again, if the wife approves