how to a pass a value to vba form filter (1 Viewer)

[MilaK]

Hello,

I'm trying to pass a value from a listbox to a filter used to filter a continuous form.

Dim ALKvalue As Long
 
 ALKvalue = Me.lstRanges.Column(5, 1)
 Debug.Print ALKvalue 'prints out 0.2

However, I get an error when I build the filter criteria with the following statement. "Access doesn't recognize ALKvalue as a fields."

If (Me.chk_ALK = True) Then

 Criteria = Criteria & "([ALK] >= ALKvalue) AND "
     
End If

However,

If (Me.chk_ALK = True) Then

 Criteria = Criteria & "([ALK] >= 0.2) AND "
     
End If

works fine.

What am I doing wrong?

Thanks,

Mila

 

[Ranman256]

I filter like this:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

 

[jdraw]

In this example

If (Me.chk_ALK = True) Then

 Criteria = Criteria & "([ALK] >= ALKvalue) AND "
     
End If

This "([ALK] >= ALKvalue) AND " is all within quotes, and is considered a literal string.

You want the value of ALKvalue so would need something like

 "([ALK] >= " & ALKvalue & ") AND "