By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Help needed with Query criteria and combo boxes

P: 2
Hi
I'm using Acces 2003
I have Query where the several parameters for the criteria are supplied from a form. One parameter source is a combo box. What I am trying to do is if:

An item is selected on the combo box then that value is used as the criteria

If user does not want the contents of the combo box used, then Query should ignore that field.

I have tried to do this with an iif statement in the query criteria and a control box on the form.

i.e in the criteria for that field use

iif([forms]![formname]![option10]=-1,[forms]![formname]![combo0],Like"*")

[option10] being the control

This works when the condition is true, ie a person wants to use the combo box and has selected a value. It does not work for the false condition. What I was expecting to happen is since that the control box is not selected(iif is false) all records are returned by the query using like"*".

Any suggestions, alternative approaches?

Thanks
Apr 4 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Hi
I'm using Acces 2003
I have Query where the several parameters for the criteria are supplied from a form. One parameter source is a combo box. What I am trying to do is if:

An item is selected on the combo box then that value is used as the criteria

If user does not want the contents of the combo box used, then Query should ignore that field.

I have tried to do this with an iif statement in the query criteria and a control box on the form.

i.e in the criteria for that field use

iif([forms]![formname]![option10]=-1,[forms]![formname]![combo0],Like"*")

[option10] being the control

This works when the condition is true, ie a person wants to use the combo box and has selected a value. It does not work for the false condition. What I was expecting to happen is since that the control box is not selected(iif is false) all records are returned by the query using like"*".

Any suggestions, alternative approaches?

Thanks
That's because iif returns a string and a string is never evaluated. Use:
Expand|Select|Wrap|Line Numbers
  1. Like iif([forms]![formname]![option10]=-1,[forms]![formname]![combo0],"*")
Apr 4 '07 #2

P: 2
Hi
Thanks, But what happens now is that the false condition works and the true condition does not. It seems that for example the criteria does not evaluate

Like oven#1

the same as [forms]![formname] etc.

oven #1 being the selection from the combo box
Would the # be the problem in the value?

thanks
Apr 4 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
The following characters would cause problems because the Like operator uses wildcards.
Expand|Select|Wrap|Line Numbers
  1. *
  2. #
  3. [ ]
  4. ?
  5. _
  6. %
  7.  
So if you want to return oven#1 then the combobox has to pass oven[#]1 to the query.
Apr 4 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.