473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help needed with Query criteria and combo boxes

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
3 2245
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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

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

Similar topics

6
by: EJC | last post by:
Hi Folks, I've been trying to build a query from a form of dialogue boxes I have created that holds search criteria for my main database. I have been able to get the query to retrieve the data...
5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
3
by: arthur-e | last post by:
Thanks in advance!! I've looked at fConcatFld but that doesn't seem to be what i'm looking for or I just can't figure it out. I'm trying to have a button select records based on two combo boxes -...
4
by: meganrobertson22 | last post by:
Hi Everyone- I have a question about how to add and then use the "All" selection in a combo box. I am trying to figure out how to: (1) add "All" as a selection to a combo box and then (2)...
12
by: Tom | last post by:
Hello, I have a database of employee data in access and I am trying to create a form with combo boxes for criteria (ex. gender, office, position, etc.) that let the user select criteria from...
2
by: Mark Roughton | last post by:
I have a form where the users need to view records for various criteria, one of which is a date field on which they may wish to view all related data for the selected date, for all dates upto and...
5
by: deaconj999 | last post by:
Hi, I have nearly finished my database and I would like to add a query that uses a combo box to get the results, not the usual paramater style input. I suppose it would need a form and a query...
4
by: Swinky | last post by:
I am trying to make a combo box dependent on a text box. I cannot get the combo box to pull the related data from the text box. I have been all over this user group & have tried several versions...
4
kcdoell
by: kcdoell | last post by:
I have a form (Form1) that displays records based on 3 unbound combo boxes that are also on my form. My record source for this form is a query “ReQryForecast” (which looks at those combo boxes via...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.