Link to home
Start Free TrialLog in
Avatar of iainmacleod
iainmacleodFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Applying Filter on Combo Box for all values less than X

Hello,
I have a form with a subform and I need to be able to filter records on the subform to show all records with a value Less than the value entered in my combo box.
I have the following code that works for exact values. I am at a loss as to how to do the Less than.
The other Values work ok, my combo box is "Tstock"

Hopefully I have explained myself.... Thanks in advance for any assistance


Dim BRAND As String
Dim CAT As String
Dim BN As String
Dim TS As String


If IsNull(Me.BRAND.Value) Or Me.BRAND.Value = "ALL" Then
    BRAND = "Like '*'"
Else
    BRAND = "='" & Me.BRAND.Value & "'"
End If

If IsNull(Me.category.Value) Or Me.category.Value = "ALL" Then
    CAT = "Like '*'"
Else
    CAT = "='" & Me.category.Value & "'"
End If

If Me.batch.Value = "ALL" Then
  BN = "Like '*'"
Else
    BN = "=" & Me.batch.Value
End If

If Me.Tstock.Value = "ALL" Then
    TS = "Like '*'"
Else
    TS = "=" & Me.Tstock.Value
End If

Me.StockMaintenanceSubForm.Form.Filter = " [stockbrand]" & BRAND & " AND [category]" & CAT & " AND [BATCH]" & BN & " AND [STOCKTOTAL]" & TS
Me.StockMaintenanceSubForm.Form.FilterOn = True

Open in new window

Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try this:

If Me.Tstock.Value = "ALL" Then
    TS = "Like '*'"
Else
    TS = " < " & Me.Tstock.Value
End If
If Nulls are possible in Me.TStock:

If Me.Tstock.Value = "ALL" Then
    TS = "Like '*'"
Else
    TS = " < " & NZ(Me.Tstock.Value,0)
End If 

Open in new window

and if Tstock is text (I have been assuming numeric this far):

If Me.Tstock.Value = "ALL" Then
    TS = "Like '*'"
Else
    TS = " < '" & Me.Tstock.Value & "'"
End If 

Open in new window

Avatar of iainmacleod

ASKER

Hi, aha, That works thanks. Ideally I would like to be able to have this filter show values between certain numbers. For example show all items that have between 25 and 50 Tstock etc. Would you have any clues...?
Instead of using a single combo box, use two textboxes (TstockLow and TstockHigh) to define the limits.

- If they are both blank, show all records
- If one of them is blank, make the high or low limit "open-ended" depending on which is left blank.
- If both are populated, use those as endpoints for the range.

The code would look something like this:

If Me.TstockLow & "" = ""  and Me.TstockHigh & "" = ""Then
    TS = "Like '*'"
Else
    TS = " BETWEEN " & NZ(Me.TstockLow, somearbitraryveryLOWvalue)  & " AND " & NZ(Me.TstockHigh, somearbitraryveryHIGHvalue)
End If 

Open in new window


The NZ function replaces nulls in those boxes with arbitrary low or high values You need to choose those yourself - the idea being that they are outside of the normal range for your data, making the limits "open-ended" .
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much that works perfectly....