Microsoft Access - Combo Box - Query

Hello,

I want to build a query which uses input from a combo box having the foll. operators as options:
= , >=, <=, > and <. Now, one way of doing it is to build different queries for each of the operators, but that would be an inefficient way of doing it.

How do I connect these different options to a single query?

Thank you for your time,

Nikhil

Hi...

You can have a single Query like this
Here i am selecting the Combox and taling the salary value from another text box...
  string Query = "select * from Emp where salary" + Combobox.Selectedvalue + " " + txtName.Text;

Hope this helps You........

 on your combo box you can give the option as
 <asp:dropdown id="dd" runat="Sever">
 <asp:itemlist value="="> =</itemlist>
.... , >=, <=, > and <.

 like this you can create drop down box user can select that and base one the selected value you can run the query (pass parameter)

 sql ="select * from user where sal '"+dd.selectedValue +"' 100 "

 here user salary will be retrieve base on the user drop down value

Based on your selection in ComboBox control, you can add operator in query,

 you can build query like this- 

 "select col1,col2 from tablename where colname " + ComboBox1.Text + "  value ";

I hope this will help you.

This is what I am using currently for >=, similarly for < and so on..

SELECT List.Feld3, List.Feld7, Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3) AS Expr1
FROM List
WHERE (((Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3))>=[Forms]![Filter]![Text23]) AND ((InStr(1,[Feld3],"/"))>3))
ORDER BY Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3);

Now, as you suggested I tried the following:

SELECT List.Feld3, List.Feld7, Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3) AS Expr1
FROM List
WHERE (((Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3))=[Forms]![Filter]![cmb_tw_sel].[Text]+[Forms]![Filter]![Text23]) AND ((InStr(1,[Feld3],"/"))>3))
ORDER BY Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3);

However, this is not working. Can you suggest me the correct syntax?

thanks,

Where exactly are these options located in the property sheet of the combo box?

I do not understand what you mean by :
<asp:dropdown id="dd" runat="Sever">
 <asp:itemlist value="="> =</itemlist>
.... , >=, <=, > and <.

Relational operators are part of the structure of the query and cannot be changed on the fly.  Querydefs are "compiled" when they are saved and part of that process includes producing a plan for how to produce the requested results and the relational operator may have an influence on that decision.

Alright. Thanks, I get the idea. I will implement it using If...Else...

Now I tried using SQL statements in VB instead of running the Queries built in the design view.

I used the following but I am getting a run time error 13.

Dim strquery_tw as String

strquery_tw ="SELECT List.Feld3, List.Feld7 FROM List WHERE (((Mid([Feld3],((InStr(1,[Feld3],"/"))-3),3))=[Forms]![Filter]![Text19]) AND ((InStr(1,[Feld3],"/"))>3));"  // in a single line

DoCmd.RunSQL strquery_tw

why am I getting the run time error. ?

Thanks,

Strings must be enclosed in single or double quotes.

I tried the following string but I am still getting an error sometimes and whenever I open the form, it asks me for a value for the Forms!Filter!Text19. Also, I tried putting ( ) around [Forms]![Filter]![Text19], but it does not make any difference. Can  you help me out ? Also, when it runs. I get a warning saying that the query is too complex.

Also, I was curious to know if it is okay to use the SQL by creating a query in the design form and then just copy paste the query in the VBA window. Do I need to delete the query once I have used its SQL in my code?

Sorry but I am getting distressed with this unpredictable behavior of Access...

Thanks,

This is not a bug.  The syntax of the query is incorrect.  Stop the code at the statement after the one that builds the string.  Print the string in the immediate window and copy it and paste it into the SQL window.  That should make it easier to find the syntax error.

Do you have any online reference for creating QueryDefs? I looked through the Access 2007 Bible book, but I could not find anything worthwhile.

Thanks,

Querydefs are stored queries.  You can use the graphical interface to build them or SQL view if you like typing.  In most cases you can switch back and forth between views but there are some constructs that the GUI does not support such as Union queries, pass-through queries, and queries that use non-equi joins.  I generally use design view to build the basic select clause because I don't like to type and I don't want to have to remember every column and table name.  Once the select part is built, I may switch to SQL view if the selection criteria is complex.  If it is simple, I stick with the designer and never switch views.