Hi,
Can someone please check this code to and give me some input to see why it is failing.
I am filtering a form for entity and cocode and the codes is selecting the block for entity, benefits and cocode
Can someone please check this code to and give me some input to see why it is failing.
I am filtering a form for entity and cocode and the codes is selecting the block for entity, benefits and cocode
Code:
Private Sub btnfilterdata_Click()
'On Error GoTo Problem:
Dim FilterData As String
Dim InvYR As Integer
Dim InvAmt As Currency
Dim InvNO As String
Dim InvMonth As Integer
Dim Entity As Integer
Dim CoCode As Integer
Dim Benefits As Integer
Dim RecordCount As Long
Dim SQLCount As String
Entity = Nz(Me.cboentitysearch, 0)
CoCode = Nz(Me.cbococodesearch, 0)
Benefits = Nz(Me.cbobenefitssearch, 0)
InvYR = Nz(Me.txtinvoiceyear)
InvAmt = Nz(Me.txtinvoiceamount)
InvNO = Nz(Me.txtinvoicenumber, 0)
InvMonth = Nz(Me.cboinvoicemonth)
'InvAmt = Nz(Me.txtinvoiceamount)
Dim STRWhere As String
Select Case True
'filtering by Entity
Case Not IsNull(Entity) And (CoCode = 0 And Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & ""
'filtering by benefits
Case Not IsNull(Benefits) And (Entity = 0 And CoCode = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.benefitsid = " & Nz(Benefits, 0) & ""
'filtering by Company Code
Case Not IsNull(CoCode) And (Entity = 0 And Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.companyID = " & Nz(CoCode, 0) & ""
'Filtering by Entity and Benefits
Case Not IsNull(Benefits And Entity) And (CoCode = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
& " AND tbbilling.benefitsid = " & Nz(Benefits, 0) & ""
'
' [B][I] 'Filtering by Entity, Benefits and Company Code[/I][/B]
Case Not IsNull(Entity And Benefits And CoCode) And (InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
& " AND tbbilling.benefitsid = " & Benefits & "" _
& " AND tbbilling.companyID = " & Nz(CoCode, 0) & ""
[B][I]'Filtering by Entity and Company code[/I][/B]
Case Not IsNull(Entity And CoCode) And (Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
& " AND tbbilling.companyID = " & Nz(CoCode, 0) & ""
'Filtering by Benefits and Company code
Case Not IsNull(Me.cbobenefitssearch And Me.cbococodesearch)
STRWhere = " WHERE tbbilling.benefitsid = " & Nz(cbobenefitssearch, 0) & "" _
& " AND tbbilling.companyID = " & Nz(cbococodesearch, 0) & ""
'
' Case InvYR <> 0
'
'
' STRWhere = " WHERE Year(tbbilling.Invoicedate) = " & InvYR & ""
'
' Case InvMonth <> 0
'
' STRWhere = " WHERE Month(tbbilling.Invoicedate) = " & InvMonth & ""
'
' Case InvAmt <> 0
'
' STRWhere = " WHERE tbbilling.Invoiceamount LIKE " & InvAmt & " "
'
' Case InvNO <> 0
'
' STRWhere = " WHERE tbbilling.InvoiceNumber LIKE '*" & InvNO & "*' "
'
Case Else
FilterData = " SELECT * " _
& "FROM tbbilling" _
& " ORDER BY tbbilling.Entityid, tbbilling.benefitsid DESC, tbbilling.invoicedate "
Me.subformbenefitstest.Form.RecordSource = FilterData
Me.subformbenefitstest.Form.Requery
Exit Sub
End Select
FilterData = " SELECT * " _
& "FROM tbbilling" _
& STRWhere _
& " ORDER BY tbbilling.Entityid, tbbilling.benefitsid DESC, tbbilling.invoicedate ASC "
Me.subformbenefitstest.Form.RecordSource = FilterData
Me.subformbenefitstest.Form.Requery
Last edited by a moderator: