related to false result while filtering form

[ritimajain]

hi .. i need your help once again.


i have form which filters record on the basis of selection in combobox. but sometimes it is giving false result lyk when i put 0 in CHAP combobox and click apply filter it is showing 0 records in report . i dont know why . please help
for ur reference i m uploading my db

[pbaldy]

No records meet all the criteria:

[CHAP] =0 AND [MC] Like '*' AND [ACSTATE] Like '*' AND [SLEVEL] Like '*' AND [STIME] > 0 AND [EXCHGR] > 0 AND [BCCH] > 0 AND [CBCH] > 0

[ritimajain]

no, in my form i can put criteria in any 1 combobox , there is no need to put criteria in all comboboxes.. when i apply filter in table CHADMIN1 in field CHAP and selected 0 it comes 101 records but when i applied same this to my form in my report it should give 101 reords but it is giving false records and showing 0 records

[pbaldy]

What you see is the string from just entering 0 for the CHAP combo. I think you may want to reexamine how you build the string.

[pbaldy]

I should have added this as a helpful tool in seeing what the string results in:

BaldyWeb-Immediate window

[ritimajain]

when i tried As you suggested in immediate window i get
[CHAP] =0 AND [MC] Like '*' AND [ACSTATE] Like '*' AND [SLEVEL] Like '*' AND [STIME] > 0 AND [EXCHGR] > 0 AND [BCCH] > 0 AND [CBCH] > 0
is it is raeson of getting 0 records because [EXCHGR] , [BCCH], [CBCH] Are null for CHAP=0 ??

if yes , how can i make changes so that does not show 0 records like this ?

[pbaldy]

Personally, if nothing is entered for an option, I leave that field out completely. Given your example, the only field in my filter would be CHAP.

[pbaldy]

The sample db here demonstrates one way:

http://www.baldyweb.com/BuildSQL.htm

[ritimajain]

i m new to access .. i m unable to get what samble db demonstrate..
tell me a way to change my present SQL code so that it also include null value like?
If IsNull(Me.cboCHAP.Value) Then
strCHAP = "> 0 "
Else
strCHAP = "=" & Me.cboCHAP.Value & " "
End If
If IsNull(Me.cboMC.Value) Then
strMC = " Like '*' "
Else
strMC = "='" & Me.cboMC.Value & "' "
End If
If IsNull(Me.cboACSTATE.Value) Then
strACSTATE = " Like '*' "
Else
strACSTATE = "='" & Me.cboACSTATE.Value & "' "
End If
If IsNull(Me.cboSLEVEL.Value) Then
strSLEVEL = " Like '*' "
Else
strSLEVEL = "='" & Me.cboSLEVEL.Value & "' "
End If
If IsNull(Me.cboSTIME.Value) Then
strSTIME = "> 0 "
Else
strSTIME = "=" & Me.cboSTIME.Value & " "
End If
If IsNull(Me.cboEXCHGR.Value) Then
strEXCHGR = "> 0 "
Else
strEXCHGR = "=" & Me.cboEXCHGR.Value & " "
End If
If IsNull(Me.cboBCCH.Value) Then
strBCCH = "> 0 "
Else
strBCCH = "=" & Me.cboBCCH.Value & " "
End If
If IsNull(Me.cboCBCH.Value) Then
strCBCH = "> 0 "
Else
strCBCH = "=" & Me.cboCBCH.Value & " "
End If
like when i entered
[CHAP] =0 AND [MC] Like '*' AND [ACSTATE] Like '*' AND [SLEVEL] Like '*' AND [STIME] > 0 AND{ [EXCHGR] >0 AND [BCCH] > 0 AND [CBCH] > 0 } instead of it will be modified to [EXCHGR] = null AND [BCCH]= NULL AND [CBCH]= NULL .. so that it can also pick null values and meet the criteria .. how can i make changes in above code?

[ritimajain]

see i have changed SQL to
If IsNull(Me.cboCHAP.Value) Then
strCHAP = "> 0 Or Null"
Else
strCHAP = "=" & Me.cboCHAP.Value & " "
End If
If IsNull(Me.cboMC.Value) Then
strMC = " Like '*' Or Null"
Else
strMC = "='" & Me.cboMC.Value & "' "
End If
If IsNull(Me.cboACSTATE.Value) Then
strACSTATE = " Like '*' Or Null"
Else
strACSTATE = "='" & Me.cboACSTATE.Value & "' "
End If
If IsNull(Me.cboSLEVEL.Value) Then
strSLEVEL = " Like '*' Or Null"
Else
strSLEVEL = "='" & Me.cboSLEVEL.Value & "' "
End If
If IsNull(Me.cboSTIME.Value) Then
strSTIME = "> 0 Or Null"
Else
strSTIME = "=" & Me.cboSTIME.Value & " "
End If
If IsNull(Me.cboEXCHGR.Value) Then
strEXCHGR = "> 0 Or Null"
Else
strEXCHGR = "=" & Me.cboEXCHGR.Value & " "
End If
If IsNull(Me.cboBCCH.Value) Then
strBCCH = "> 0 Or Null"
Else
strBCCH = "=" & Me.cboBCCH.Value & " "
End If
If IsNull(Me.cboCBCH.Value) Then
strCBCH = "> 0 Or Null"
Else
strCBCH = "=" & Me.cboCBCH.Value & " "
End If
by doing this i have to solve my previous problem , but by doing this one more problem arises .. filter stop working..
like if i put MC = ON , it is not filtering data ,, it is showing results for both mc = off and mc =on,
[CHAP] > 0 Or Null AND [MC] Like '*' Or Null AND [ACSTATE] Like '*' Or Null AND [SLEVEL] Like '*' Or Null AND [STIME] =40 AND [EXCHGR] > 0 Or Null AND [BCCH] > 0 Or Null AND [CBCH] > 0 Or Null
and if i put stime = 40 .. it is not filtering data and giving all the value of stime
[CHAP] > 0 Or Null AND [MC] Like '*' Or Null AND [ACSTATE] Like '*' Or Null AND [SLEVEL] Like '*' Or Null AND [STIME] =40 AND [EXCHGR] > 0 Or Null AND [BCCH] > 0 Or Null AND [CBCH] > 0 Or Null

i dont know why ??

[pbaldy]

The fundamental difference in the methods is that you create 8 variables and must use them all. I create 1 variable, and only add to it when a field needs to be part of the filter. That means you have to come up with some way to return all records, and I don't. Not including the field in the filter will automatically return all records.

[ritimajain]

see , my need is to create a form which uses a filter similar to excel in which user can put filter on as many fields as he wants to.. it can be on single field or as well as combination of two more fields or on all fields .. i m trying to achieve something similar to excel filter
what are the possible solutions to re frame it?

[pbaldy]

Yes, i know what you're trying to do. Like I said, I would adapt the method from the sample db.

[ritimajain]

i m nt good at coding ..can you help in writing vba code , because i m unable in adapting sample db and re frame it according to my needs ?

[June7]

You are making code more complicated than necessary.

Private Sub Command16_Click()
    Dim strFilter As String
    ' Get the values from the combo boxes
    If Not IsNull(Me.cboCHAP.Value) Then strFilter = "CHAP=" & Me.cboCHAP.Value & " AND "
    If Not IsNull(Me.cboMC.Value) Then strFilter = strFilter & "MC='" & Me.cboMC.Value & "' AND "
    If Not IsNull(Me.cboACSTATE.Value) Then strFilter = strFilter & "ACSTATE='" & Me.cboACSTATE.Value & "' AND "
    If Not IsNull(Me.cboSLEVEL.Value) Then strFilter = strFilter & " SLEVEL='" & Me.cboSLEVEL.Value & "' AND "
    If Not IsNull(Me.cboSTIME.Value) Then strFilter = strFilter & "STIME=" & Me.cboSTIME.Value & " AND "
    If Not IsNull(Me.cboEXCHGR.Value) Then strFilter = strFilter & "EXCHGR=" & Me.cboEXCHGR.Value & " AND "
    If Not IsNull(Me.cboBCCH.Value) Then strFilter = strFilter & "BCCH=" & Me.cboBCCH.Value & " AND "
    If Not IsNull(Me.cboCBCH.Value) Then strFilter = strFilter & "CBCH=" & Me.cboCBCH.Value & " AND "
    If strFilter <> "" Then strFilter = Left(strFilter, Len(strFilter) - 5)
    DoCmd.OpenReport "rptCHADMIN1", acViewReport, , strFilter, acNormal
End Sub