Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk

asked on

Variance between values, access vba

I have a form with a combo that allows me to select nom cc with a view to filtering on that [nom cc], I have a label controlled by two buttons that allow me to set the variance either 0 for exact ie if i select 1.3 then i just want [nom cc] of 1.3 but if i select .1 then I want records with [nom cc] of 1.2, 1.3 and 1.4 ie .1 over and below the selected. If the record has no [nom cc] value then that shown.

Not quite sure how to set the filter for that I have some code I think its a math problem:

Private Sub CBNom_AfterUpdate()
Dim StrFilter, StrCBIn As String
StrCBIn = Me.CBNom.Value
StrFilter = "((abs([Nom CC] - " & Me.LblVNomCount.Caption & ")" & ")<=" & Me.LblVNomCount.Caption & ") Or (isnull([nom cc]))"

Debug.Print StrFilter
Me.Form.Filter = StrFilter

Me.Form.FilterOn = True
End Sub

Open in new window

Avatar of IrogSinta
Flag of United States of America image

Try this:
Private Sub CBNom_AfterUpdate()
    Dim strFilter, dblCBIn As Double
    dblCBIn = Me.CBNom.Value
    strFilter = "(Abs([Nom CC] - " & dblCBIn & ") <= " & Me.LblVNomCount.Caption & ") Or (IsNull([nom cac]))"
    Me.Filter = sttFilter
    Me.FilterOn = True
End Sub

Open in new window

Avatar of PeterBaileyUk


the maths is still incorrect
What results are you getting?
Is your data type for [nom cc] set to Number in the table?
Avatar of als315
Flag of Russian Federation image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you and happy new year