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
Me.Form.Requery
End Sub

Open in new window

Microsoft Access

Last Comment

PeterBaileyUk

8/22/2022 - Mon

IrogSinta

1/4/2013

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

PeterBaileyUk

1/5/2013

ASKER

the maths is still incorrect

IrogSinta

1/5/2013

What results are you getting?

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

IrogSinta

1/5/2013