Record with blank field not visible after using filter

[joshi868b]

i have a form with a subform and few combo box to use as a filter the data. when there is a blank field in the table it is not shown my coding for the filter is
[

Function searchcriteria()
Dim device, vlan As String
Dim task, strciteria As String


If IsNull(Me.cbodevice) Then
device = "[DEVICE NAME] like '*'"
Else
device = "[DEVICE NAME]= '" & Me.cbodevice & "'"
End If


If IsNull(Me.cbovlan) Then
vlan = "[VLAN ID] like '*'"
Else
vlan = "[VLAN ID]= '" & Me.cbovlan & "'"
End If
strcriteria = device & "And" & vlan
task = "select * from L2PORTDETAILS where " & strcriteria
Me.L2PORTDETAILS_subform.Form.RecordSource = task
Me.L2PORTDETAILS_subform.Form.Requery


End Function]

 

[Joe4]

Cross-posted: Record with blank field not visible after using filter
and http://www.access-programmers.co.uk/forums/showthread.php?t=288205

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[xenou]

There's two criteria with And so you wouldn't show any results under certain circumstances (i.e., even if one field is blank but the other has no matching criteria then you get no results because AND must match on both criteria). Also if by blank you mean Null then Null is not included in Like '*' . So you must use an NZ function or other strategy to check for nulls.

Lol Posted in three forums. You usually get no answers when you cross post so I recommend you post links to your related posts when you do that.

 

[joshi868b]

dear xenou thnx for replying i am new to vba can u tell me the exact code plz

 

[xenou]

If IsNull(Me.cbodevice) Then
device = "NZ([DEVICE NAME],'') like '*'"

If IsNull(Me.cbovlan) Then
vlan = "NZ([VLAN ID],'') like '*'"

 

[joshi868b]

I TRIED IT But while running it show syntax error(missing operator) in query expression [DEVICE NAME] = 'Name' and nz([VLANID].")LIKE'*"

 

[xenou]

Post your code (what you have posted is indeed bad syntax but it's also incomplete and I have no idea what your actual code looks like).

 

[joshi868b]

Function searchcriteria()
Dim device, vlan As String
Dim task, strciteria As String






If IsNull(Me.cbodevice) Then
device = "NZ([DEVICE NAME],'') like '*'"
Else
device = "[DEVICE NAME]= '" & Me.cbodevice & "'"
End If




If IsNull(Me.cbovlan) Then
vlan = "NZ([VLAN ID],'') like '*'"
Else
vlan = "[VLAN ID]= '" & Me.cbovlan & "'"
End If
strcriteria = device & "And" & vlan
task = "select * from L2PORTDETAILS where " & strcriteria
Me.L2PORTDETAILS_subform.Form.RecordSource = task
Me.L2PORTDETAILS_subform.Form.Requery




End Function

 

[joshi868b]

Private Sub cbodevice_AfterUpdate()
Call searchcriteria
End Sub


Private Sub cbovlan_AfterUpdate()
Call searchcriteria
End Sub






Private Sub CLEAR_Click()
Me.cbodevice = Null
Me.cbovlan = Null
Me.Refresh
Me.L2PORTDETAILS_subform.Form.Requery
End Sub


Private Sub Form_Load()
'DoCmd.Maximize
End Sub


Function searchcriteria()
Dim device, vlan As String
Dim task, strciteria As String






If IsNull(Me.cbodevice) Then
device = "NZ([DEVICE NAME],'') like '*'"
Else
device = "[DEVICE NAME]= '" & Me.cbodevice & "'"
End If




If IsNull(Me.cbovlan) Then
vlan = "NZ([VLAN ID],'') like '*'"
Else
vlan = "[VLAN ID]= '" & Me.cbovlan & "'"
End If
strcriteria = device & "And" & vlan
task = "select * from L2PORTDETAILS where " & strcriteria
Me.L2PORTDETAILS_subform.Form.RecordSource = task
Me.L2PORTDETAILS_subform.Form.Requery




End Function

 

[xenou]

THis looks like it should have spaces around the AND:
strcriteria = device & "And" & vlan

should be:
strcriteria = device & " And " & vlan

That might help (might not). You need to make sure of the string you are creating in your code, that it is valid SQL. In other words, output strcriteria (or better yet task) to the immediate window so you can see exactly what the string is that you create from your variables. And to make sure it works, you should be able to verify that by actually running it as a sql command on your database.