add WHERE clause in row source query of a listbox 1

[davyre]

Hi,
I have a listbox that is populated using this query (the row source)

SELECT TblCustOrder.CustOrderID, TblCustOrder.CustomerID, TblCustomer.CustomerName, TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustOrderDate
FROM TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID
ORDER BY TblCustOrder.CustOrderDate DESC;

So when the form is open, the listbox will have its records. But what I want is when I press a button, it will add filter/condition clause so it will show records that based on the filter (for example, add WHERE TblCustOrder.CustOrderDate>10/05/2012, so the listbox will only show records that has custOrderDate later than 10/05/2012)
how can I do that?is it possible to do this?

 

[dhookom]

You can use code to update the Row Source property of the combo box. Where do you expect the 10/05/2012 to come from? Where do the filter values come from?

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Hi Duane,

can you give me an example of the code to update row source property?
the filter values will come from another control, but in this case to simplify the problem, I use 10/05/2012.
Let's say the value will be stored on a variable called FilterDate

so

Dim FilterDate as date
Dim UpdQuery as string
FilterDate=Me.DateBox.Value

'updating row source ->UpdQuery ->need help on this

Me.OrderListBox.Requery

 

[dhookom]

Dim strSQL as String
Dim strWhere as String
If not IsNull(Me.DateBox) Then
   strWhere = " WHERE CustOrderDate >= #" & Format(Me.DateBox,"dd-mmm-yyyy")& "# "
End If
strSQL = "SELECT TblCustOrder.CustOrderID, TblCustOrder.CustomerID, TblCustomer.CustomerName, " & _
   "TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustOrderDate " & _
   "FROM TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID " & _
   strWhere & _
   "ORDER BY TblCustOrder.CustOrderDate DESC; "
Debug.Print strSQL
Me.OrderListBox.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

thanks!

Just one more, I have two tables TblCustOrder and TblCustOrderUnit.
TblCustOrder(CustOrderID, CustomerID, CustOrderDate,etc)
TblCustOrderUnit(CustOrderUnitID, CustOrderID, UnitID, etc)
so TblCustOrder have relationship one to many to TblCustOrderUnit, with CustOrderID as Foreign key

Question is the strWhere, how to select all order from TblCustOrder where UnitID=A? Note that A is a variable from a control, UnitID is in the TblCustOrderUnit..

so far I got:
SELECT TblCustOrder.CustOrderID, TblCustOrder.CustomerID, TblCustomer.CustomerName, TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustOrderDate
FROM TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID
ORDER BY TblCustOrder.CustOrderDate DESC
WHERE.........................

 

[dhookom]

Is this question still about the row source of a listbox?
Is it possible for A to be Null?
Is A/UnitID a string or numeric?
Why are you providing "A" rather than the name of a control?
Why did you choose to name it "A" since "A" has no value other than being the first letter of the alphabet? Why not strUnitID or lngUnitID?

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

yes, this is still about the row source. After some consideration, I chose to use UnitID instead of Date as the filter.
Nope, A is impossible to be null as it is a value from another listbox that user must select.
OK, I changed the name from A to selectionUnitID. I chose A because this is just a prototype function in my database, so I thought any name is okay.

I got this:

Dim selectionUnitID as Long
updListQry = "SELECT TblCustOrder.CustOrderID, TblCustOrder.CustomerID, TblCustomer.CustomerName, TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustOrderDate " _
            & "FROM (TblCustomer INNER JOIN TblCustOrder ON TblCustomer.CustomerID = TblCustOrder.CustomerID) INNER JOIN TblCustOrderUnit ON TblCustOrder.CustOrderID = TblCustOrderUnit.OrderID " _
            & "WHERE TblCustOrderUnit.UnitID=" & selectionUnitID _
            & "ORDER BY TblCustOrder.CustOrderDate DESC;"

I tried the query, and after I click the button, and the listbox source now should be ^ . But the listbox now is empty, where it should not because I have checked that there should be 3 orders with that particular unitID.
Please tell me if you need to see the whole code. THanks!

 

[davyre]

Never mind, I have found the problem with my updListQry,
it needed space between WHERE clause and ORDER BY clause, which I did not provide. It should be
& "WHERE TblCustOrderUnit.UnitID=" & selectionUnitID[highlight #FCE94F] & " "[/highlight] _
& "ORDER BY TblCustOrder.CustOrderDate DESC;"

THanks!