How do I query any combination of 7 fields - 4cbo and 3 txt

[Guest]

Hi guys, bit of a tricky one here and I can't seem to find satisfactory
answers anywhere so hoping you can help.

I have a form which contains 7 parameters. 4 combo boxes and 3 text fields.
The user selects entries on any of the combo boxes or types text in any of
the text fields. They do not have to fill in all 7, it could be any
combination of the 7.

Under this form is a sub form which will display results according to which
fields are set above but how do I code my query to accept any of the above?

My main form is called Orders_Queried and my sub form is called
Order_Query_Results

The 7 fields are:

cboQueriedCustomer
cboQueriedContact
cboOrder_Complete
cboProduct
txtQueriedCustRef
txtDescription
txtTypeColourSize

The first part of my query is:

SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE

Now obviously my query needs to be set to show results where fields are
equal to the combo box entries, but for the txt fields then the results need
to be like. But how do I code this so it will display the results no matter
which combination of parameters are set.

 

[Michel Walsh]

Hi,


It is probably better to make a statement at run-time than to use a generic
one filled with ... OR ... IS NULL. The generic one will be probably very
poorly optimized, in general.



strSQL="... WHERE true"

If 0<>len( Me.ControlForField1Criteria & vbNullString) then strSQL =
strSQL & " AND f1=FORMS!formNameHere!ControlForField1Criteria"
if 0<>len( Me.ControlForField2Criteria & vbNullString) then strSQL =
strSQL & " AND f2=FORMS!formNameHere!ControlForField2Criteria"
....



At the end, strSQL is the string representation of your SQL statement. Use
it as rowsource, controlsource, or otherwise as it fits your need. Since I
used the syntax FORMS!formName!ControlName, if may be inappropriate in some
situations. In those case, move the expression out of the string, but be
careful to include the appropriate delimiters, if any is required. The
actual syntax should work for rowsource (of listbox or combobox), and with
DoCmd object, but won't work with CurrentDb object, as example.


Hoping it may help,
Vanderghast, Access MVP

 

[Duane Hookom]

I would add a command button that would refresh the subform. The code would
be something like (caution air-coding):

Dim strWhere as String
Dim strSQL as String
strSQL ="SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order
Date], " & _
"Order_Details.[Item Number], Order_Details.[Item Type], " & _
"Order_Details.Description, Order_Details.Type_Colour_Size, " & _
"Order_Details.Quantity, Order_Details.Price,
Order_Details.Denomination, " & _
"Order_Details.Department, Orders.Complete "
strSQL = strSQL & "FROM Orders INNER JOIN Order_Details " & _
"ON Orders.OrderNumber = Order_Details.OrderNumber " & _
"WHERE "
strWhere = "1 = 1 "
If Not IsNull(Me.cboItem) Then
strWhere = strWhere & " And [Item Number] = " & Me.cboItem
End If
If Not IsNull(Me.cboCustID) Then
strWhere = strWhere & " And [CustomerID] = " & Me.cboCustID
End If
'etc
Me.sfrmMySub.form.RecordSource = strSQL & strWhere