Where Criteria

[ZEEq]

Hello Everyone

My question is what's the correct syntax to add Or criteria to show all grades if cbograde is not selected?

" Where [tblStudentClass].GradeID= " & Me.cboGrade

 

[pbaldy]

Since you appear to be building the SQL in code, simply don't add a WHERE clause if the combo hasn't been selected.

 

[Ranman256]

if you show all records in the form, then cycle thru the controls to see what ones to use.
then filter the records.
(or further down add the filter to a query and open the query. Note: the query must already exist.)

Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

sWhere = "1=1"

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif


'---------------
'or 'BUILD the query from the 'where'
'---------------
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany WHERE " & sWhere
qdf.Close

  'open the query here!
DOCMD.openquery kQRY

 

[ZEEq]

Since you appear to be building the SQL in code, simply don't add a WHERE clause if the combo hasn't been selected.

and what if i want to see specific records? from grades category

 

[Ranman256]

open the form that shows grades,
run the filter.

 

[pbaldy]

and what if i want to see specific records? from grades category

The type of thing I'm talking about:

strSQL = "SELECT ... FROM ..."

If <TestForComboContainingValue> Then
 strSQL = strSQL & "WHERE..."
End If

In other words, you filter if there's a selection in the combo, otherwise not.

 

[ebs17]

As a variant you can also use ...

...
" Where tblStudentClass.GradeID= " & Me.cboGrade & " Or " & Me.cboGrade & " Is NULL"

You shouldn't use this for a large number of criteria at the same time, because checking where there is nothing to check is also an effort that has to be managed.