Variable in where clause

[franc_5791]

Hi All

I am struggling with a query in my form

I have a Listbox and a text box on the form.

The user has select one value from the Listbox and enter a value in text box

My query should be

select * from mytable where variable1 (Listbox) = variable2 (text box)

I am at crossroads here and need help with the query. How do I form the query and both variables are string type

Help is appreciated.

Best wishes

 

[Premy]

Well, why would u want to reenter a value in textbox which is already selected in Listbox. Imagine a Car table; you're basically trying to do "Select * FROM tblCars WHERE BMW = BMW.

One would rather expect something like "Select * FROM tblCars WHERE CarName = BMW"

Or:
"Select * FROM tblCars WHERE CarName = BMW AND Year = 2008"

In the 1st case BMW would come from Listbox and in the second BMW would come from Listbox (though a comboBox looks more appropriate) and Year would come from TextBox (here too a second combo would do better).
So what data you're really trying to pull from your table??

 

[WayneRyan]

franc,

Staying with the car analogy, if you have a table:

tblCars
=======
ID - AutoNumber
Make - (Ford, Chevy, Chrysler)
Model - (Pinto, Vega, Camaro)
Type - (Truck, Sedan, Van)

If you want to select either (Make, Model, or Type) in your Listbox AND
enter some text to correspond to the sets in parentheses above ... it's EASY!

Just not in the query grid.

The Query Grid works with "fixed" columns and criteria. You can't have a
column be a "Make" one minute, then a "model" the next.

Use a QueryDef:

Dim MyQDF As DAO.TableDef
Set MyQDF = QueryDefs("YourCarQuery")
MyQDF.SQL = "Select * From tblCars Where " & Me.ListBox & " = '" & Me.txtBox & "'"
MyQDF.Close

Note that you don't need quotes around the ListBox entry.

hth,
Wayne

 

[franc_5791]

Thanks for your response. You definitely make sense

Consider the below given scenario

I have 10000 order details in my table raised by 200 different sales persons

Now, my Listbox will have two options or values. Sales_person and Order Id

If I want all orders raised by a sales person, as you said, if I use a List box to select a sales person's name, I need to have all the 200 guys in the list box and user has to search for the name. This is not good practice. Is it ?

So I want to have a Listbox with values Order_id and Sales_ person and enter the actual order id or name of the sales person in the text box.

so the query is

select * from mytable where variable1 = variable2

when variable1 is either order_id or Sales_person from List box and variable2 is the corresponding value i.e

for exp 123456 if order_id is selected in listbox
XYZ if sales_person is selected in Listbox

Hope I am clear.

Regards
P

 

[Premy]

Well I can't quite see how u would go about choosing either OrderID or SalesPerson in just 1 listbox. Do you have an option group with radio buttons or something like that?

If I could make a suggestion I'd say it'd be much easier to create a combo "cboSalesPerson" with 2 columns: SalesPersonID and SalesPersonName. These are of course populated thru your SalesPersons Table, and the first column is hidden, so only the name shows up. 200 names are not much for a combo since you need only to type the first letter of the salesman's name to jump directly to it or at least get near it. U can also create code to jump after the first two or three letters are selected, to narrow down even more.

Now u could code this combobox's afterupdate event as follows:

Private Sub cboSalesPerson_AfterUpdate()
Me.cboOrders.Rowsource = "Select OrderID FROM tblOrders WHERE SalesPersonID =" & Me. cboSalesPerson & " ORDER BY OrderDate"

This would load only the orders raised by that particular sales person in the Orders combo box. U then choose the order you wishes to view.

U could narrow it down even more by creating another combobox called cboMonth and a textbox called txtYear. txtYear's default value would be Year(Date()), so always preselecting the current year. If u want results from another year u just type it in. cboMonth's rowsource would be: "SELECT Month([OrderDate]) AS Mnumber, MonthName(Month([OrderDate])) AS Mname FROM tblOrders WHERE Year(OrderDate)=" & Me.[txtYear];". (here too the first column is hidden, so only the name of the months show up in the combo). Prior to choosing a salesperson, u now choose a month and a year first. The code for cboSalesPerson will now have to look like this:

Private Sub cboSalesPerson_AfterUpdate()
Me.cboOrders.Rowsource = "Select OrderID FROM tblOrders WHERE SalesPersonID =" & Me. cboSalesPerson AND Month(OrderDate)=" & Me.cboMonth & " AND Year(OrderDate)=" & Me.txtYear & " ORDER BY OrderDate"

HTH