Report Options

Can someone please help? I have the following code and I want to exclude the
Nulls... It's returning records with a null value. I thought I wanted to
include them because I was having a problem with returning all records when
nothing was selected. Now it shows what is selected plus the Nulls.

Private Sub cmdShowList_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varWhere As Variant
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSBU As String
Dim strCurrentStatus As String
Dim strAuthority As String
Dim strAudittype As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")
varWhere = Null

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

If Not IsNull(Me.cboStructural) Then
varWhere = (varWhere + " AND ") & "([structural] IS NULL OR " _
& "[structural] = " & Quotes(Me.cboStructural) & ") "
End If


If Not IsNull(Me.cboJurisdiction) Then
varWhere = (varWhere + " AND ") & "([ideajurisdiction] IS NULL OR " _
& "[ideajurisdiction] = " &
Quotes(Me.cboJurisdiction) & ")"
End If

If Not IsNull(Me.cboBenefitType) Then
varWhere = (varWhere + " AND ") & "([benefittype] IS NULL OR " _
& "[benefittype] = " & Quotes(Me.cboBenefitType) & ")"
End If

If Not IsNull(Me.cboIdeaCategory) Then
varWhere = (varWhere + " AND ") & "([ideacategory] IS NULL OR " _
& "[ideacategory] = " & Quotes(Me.cboIdeaCategory) &
")"
End If

If Not IsNull(Me.cboHWContact) Then
varWhere = (varWhere + " AND ") & "([hwcontact] IS NULL OR " _
& "[hwcontact] = " & Quotes(Me.cboHWContact) & ")"
End If

If Not IsNull(Me.cboExternalContact) Then
varWhere = (varWhere + " AND ") & "([externalcontact] IS NULL OR " _
& "[externalcontact] = " &
Quotes(Me.cboExternalContact) & ")"
End If

If Not IsNull(Me.cboSBU) Then
varWhere = (varWhere + " AND ") & "([sbu] IS NULL OR " _
& "[sbu] = " & Quotes(Me.cboSBU) & ")"
End If

If Not IsNull(Me.cboCurrentStatus) Then
varWhere = (varWhere + " AND ") & "([currentstatus] IS NULL OR " _
& "[currentstatus] = " & Quotes(Me.cboCurrentStatus)
& ")"
End If

If Not IsNull(Me.cboAuthority) Then
varWhere = (varWhere + " AND ") & "([authority] IS NULL OR " _
& "[authority] = " & Quotes(Me.cboAuthority) & ")"
End If

If Not IsNull(Me.cboAuditType) Then
varWhere = (varWhere + " AND ") & "([audittype] IS NULL OR " _
& "[audittype] = " & Quotes(Me.cboAuditType) & ")"
End If

strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
' DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing
Debug.Print strSQL

End Sub

Rather than

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

you want

If Not IsNull(Me.cboCountry) Then
varWhere = "[Country] = " & Quotes(Me.cboCountry) & ")"
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"chickalina" wrote in message
...
Can someone please help? I have the following code and I want to exclude
the
Nulls... It's returning records with a null value. I thought I wanted to
include them because I was having a problem with returning all records
when
nothing was selected. Now it shows what is selected plus the Nulls.

Private Sub cmdShowList_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varWhere As Variant
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSBU As String
Dim strCurrentStatus As String
Dim strAuthority As String
Dim strAudittype As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")
varWhere = Null

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

If Not IsNull(Me.cboStructural) Then
varWhere = (varWhere + " AND ") & "([structural] IS NULL OR " _
& "[structural] = " & Quotes(Me.cboStructural) & ")
"
End If


If Not IsNull(Me.cboJurisdiction) Then
varWhere = (varWhere + " AND ") & "([ideajurisdiction] IS NULL OR
" _
& "[ideajurisdiction] = " &
Quotes(Me.cboJurisdiction) & ")"
End If

If Not IsNull(Me.cboBenefitType) Then
varWhere = (varWhere + " AND ") & "([benefittype] IS NULL OR " _
& "[benefittype] = " & Quotes(Me.cboBenefitType) &
")"
End If

If Not IsNull(Me.cboIdeaCategory) Then
varWhere = (varWhere + " AND ") & "([ideacategory] IS NULL OR " _
& "[ideacategory] = " & Quotes(Me.cboIdeaCategory)
&
")"
End If

If Not IsNull(Me.cboHWContact) Then
varWhere = (varWhere + " AND ") & "([hwcontact] IS NULL OR " _
& "[hwcontact] = " & Quotes(Me.cboHWContact) & ")"
End If

If Not IsNull(Me.cboExternalContact) Then
varWhere = (varWhere + " AND ") & "([externalcontact] IS NULL OR "
_
& "[externalcontact] = " &
Quotes(Me.cboExternalContact) & ")"
End If

If Not IsNull(Me.cboSBU) Then
varWhere = (varWhere + " AND ") & "([sbu] IS NULL OR " _
& "[sbu] = " & Quotes(Me.cboSBU) & ")"
End If

If Not IsNull(Me.cboCurrentStatus) Then
varWhere = (varWhere + " AND ") & "([currentstatus] IS NULL OR " _
& "[currentstatus] = " &
Quotes(Me.cboCurrentStatus)
& ")"
End If

If Not IsNull(Me.cboAuthority) Then
varWhere = (varWhere + " AND ") & "([authority] IS NULL OR " _
& "[authority] = " & Quotes(Me.cboAuthority) & ")"
End If

If Not IsNull(Me.cboAuditType) Then
varWhere = (varWhere + " AND ") & "([audittype] IS NULL OR " _
& "[audittype] = " & Quotes(Me.cboAuditType) & ")"
End If

strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
' DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing
Debug.Print strSQL

End Sub

Thanks Douglas! Perfect!

"Douglas J. Steele" wrote:

Rather than

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

you want

If Not IsNull(Me.cboCountry) Then
varWhere = "[Country] = " & Quotes(Me.cboCountry) & ")"
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"chickalina" wrote in message
...
Can someone please help? I have the following code and I want to exclude
the
Nulls... It's returning records with a null value. I thought I wanted to
include them because I was having a problem with returning all records
when
nothing was selected. Now it shows what is selected plus the Nulls.

Private Sub cmdShowList_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varWhere As Variant
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSBU As String
Dim strCurrentStatus As String
Dim strAuthority As String
Dim strAudittype As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")
varWhere = Null

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

If Not IsNull(Me.cboStructural) Then
varWhere = (varWhere + " AND ") & "([structural] IS NULL OR " _
& "[structural] = " & Quotes(Me.cboStructural) & ")
"
End If


If Not IsNull(Me.cboJurisdiction) Then
varWhere = (varWhere + " AND ") & "([ideajurisdiction] IS NULL OR
" _
& "[ideajurisdiction] = " &
Quotes(Me.cboJurisdiction) & ")"
End If

If Not IsNull(Me.cboBenefitType) Then
varWhere = (varWhere + " AND ") & "([benefittype] IS NULL OR " _
& "[benefittype] = " & Quotes(Me.cboBenefitType) &
")"
End If

If Not IsNull(Me.cboIdeaCategory) Then
varWhere = (varWhere + " AND ") & "([ideacategory] IS NULL OR " _
& "[ideacategory] = " & Quotes(Me.cboIdeaCategory)
&
")"
End If

If Not IsNull(Me.cboHWContact) Then
varWhere = (varWhere + " AND ") & "([hwcontact] IS NULL OR " _
& "[hwcontact] = " & Quotes(Me.cboHWContact) & ")"
End If

If Not IsNull(Me.cboExternalContact) Then
varWhere = (varWhere + " AND ") & "([externalcontact] IS NULL OR "
_
& "[externalcontact] = " &
Quotes(Me.cboExternalContact) & ")"
End If

If Not IsNull(Me.cboSBU) Then
varWhere = (varWhere + " AND ") & "([sbu] IS NULL OR " _
& "[sbu] = " & Quotes(Me.cboSBU) & ")"
End If

If Not IsNull(Me.cboCurrentStatus) Then
varWhere = (varWhere + " AND ") & "([currentstatus] IS NULL OR " _
& "[currentstatus] = " &
Quotes(Me.cboCurrentStatus)
& ")"
End If

If Not IsNull(Me.cboAuthority) Then
varWhere = (varWhere + " AND ") & "([authority] IS NULL OR " _
& "[authority] = " & Quotes(Me.cboAuthority) & ")"
End If

If Not IsNull(Me.cboAuditType) Then
varWhere = (varWhere + " AND ") & "([audittype] IS NULL OR " _
& "[audittype] = " & Quotes(Me.cboAuditType) & ")"
End If

strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
' DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing
Debug.Print strSQL

End Sub

using the word quotes and not "??

"Douglas J. Steele" wrote:

Rather than

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

you want

If Not IsNull(Me.cboCountry) Then
varWhere = "[Country] = " & Quotes(Me.cboCountry) & ")"
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"chickalina" wrote in message
...
Can someone please help? I have the following code and I want to exclude
the
Nulls... It's returning records with a null value. I thought I wanted to
include them because I was having a problem with returning all records
when
nothing was selected. Now it shows what is selected plus the Nulls.

Private Sub cmdShowList_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varWhere As Variant
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSBU As String
Dim strCurrentStatus As String
Dim strAuthority As String
Dim strAudittype As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")
varWhere = Null

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

If Not IsNull(Me.cboStructural) Then
varWhere = (varWhere + " AND ") & "([structural] IS NULL OR " _
& "[structural] = " & Quotes(Me.cboStructural) & ")
"
End If


If Not IsNull(Me.cboJurisdiction) Then
varWhere = (varWhere + " AND ") & "([ideajurisdiction] IS NULL OR
" _
& "[ideajurisdiction] = " &
Quotes(Me.cboJurisdiction) & ")"
End If

If Not IsNull(Me.cboBenefitType) Then
varWhere = (varWhere + " AND ") & "([benefittype] IS NULL OR " _
& "[benefittype] = " & Quotes(Me.cboBenefitType) &
")"
End If

If Not IsNull(Me.cboIdeaCategory) Then
varWhere = (varWhere + " AND ") & "([ideacategory] IS NULL OR " _
& "[ideacategory] = " & Quotes(Me.cboIdeaCategory)
&
")"
End If

If Not IsNull(Me.cboHWContact) Then
varWhere = (varWhere + " AND ") & "([hwcontact] IS NULL OR " _
& "[hwcontact] = " & Quotes(Me.cboHWContact) & ")"
End If

If Not IsNull(Me.cboExternalContact) Then
varWhere = (varWhere + " AND ") & "([externalcontact] IS NULL OR "
_
& "[externalcontact] = " &
Quotes(Me.cboExternalContact) & ")"
End If

If Not IsNull(Me.cboSBU) Then
varWhere = (varWhere + " AND ") & "([sbu] IS NULL OR " _
& "[sbu] = " & Quotes(Me.cboSBU) & ")"
End If

If Not IsNull(Me.cboCurrentStatus) Then
varWhere = (varWhere + " AND ") & "([currentstatus] IS NULL OR " _
& "[currentstatus] = " &
Quotes(Me.cboCurrentStatus)
& ")"
End If

If Not IsNull(Me.cboAuthority) Then
varWhere = (varWhere + " AND ") & "([authority] IS NULL OR " _
& "[authority] = " & Quotes(Me.cboAuthority) & ")"
End If

If Not IsNull(Me.cboAuditType) Then
varWhere = (varWhere + " AND ") & "([audittype] IS NULL OR " _
& "[audittype] = " & Quotes(Me.cboAuditType) & ")"
End If

strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
' DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing
Debug.Print strSQL

End Sub

You're the one who had the Quotes function in your original code. g

I assumed it was a function you wrote to put the appropriate quotes around
text strings, and leave them off numeric strings.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"chickalina" wrote in message
...
using the word quotes and not "??

"Douglas J. Steele" wrote:

Rather than

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) & ")"
End If

you want

If Not IsNull(Me.cboCountry) Then
varWhere = "[Country] = " & Quotes(Me.cboCountry) & ")"
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"chickalina" wrote in message
...
Can someone please help? I have the following code and I want to
exclude
the
Nulls... It's returning records with a null value. I thought I wanted
to
include them because I was having a problem with returning all records
when
nothing was selected. Now it shows what is selected plus the Nulls.

Private Sub cmdShowList_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varWhere As Variant
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strIdeaCategory As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSBU As String
Dim strCurrentStatus As String
Dim strAuthority As String
Dim strAudittype As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")
varWhere = Null

If Not IsNull(Me.cboCountry) Then
varWhere = "([country] IS NULL OR " _
& "[Country] = " & Quotes(Me.cboCountry) &
")"
End If

If Not IsNull(Me.cboStructural) Then
varWhere = (varWhere + " AND ") & "([structural] IS NULL OR "
_
& "[structural] = " & Quotes(Me.cboStructural) &
")
"
End If


If Not IsNull(Me.cboJurisdiction) Then
varWhere = (varWhere + " AND ") & "([ideajurisdiction] IS NULL
OR
" _
& "[ideajurisdiction] = " &
Quotes(Me.cboJurisdiction) & ")"
End If

If Not IsNull(Me.cboBenefitType) Then
varWhere = (varWhere + " AND ") & "([benefittype] IS NULL OR "
_
& "[benefittype] = " & Quotes(Me.cboBenefitType)
&
")"
End If

If Not IsNull(Me.cboIdeaCategory) Then
varWhere = (varWhere + " AND ") & "([ideacategory] IS NULL OR "
_
& "[ideacategory] = " &
Quotes(Me.cboIdeaCategory)
&
")"
End If

If Not IsNull(Me.cboHWContact) Then
varWhere = (varWhere + " AND ") & "([hwcontact] IS NULL OR " _
& "[hwcontact] = " & Quotes(Me.cboHWContact) &
")"
End If

If Not IsNull(Me.cboExternalContact) Then
varWhere = (varWhere + " AND ") & "([externalcontact] IS NULL
OR "
_
& "[externalcontact] = " &
Quotes(Me.cboExternalContact) & ")"
End If

If Not IsNull(Me.cboSBU) Then
varWhere = (varWhere + " AND ") & "([sbu] IS NULL OR " _
& "[sbu] = " & Quotes(Me.cboSBU) & ")"
End If

If Not IsNull(Me.cboCurrentStatus) Then
varWhere = (varWhere + " AND ") & "([currentstatus] IS NULL OR
" _
& "[currentstatus] = " &
Quotes(Me.cboCurrentStatus)
& ")"
End If

If Not IsNull(Me.cboAuthority) Then
varWhere = (varWhere + " AND ") & "([authority] IS NULL OR " _
& "[authority] = " & Quotes(Me.cboAuthority) &
")"
End If

If Not IsNull(Me.cboAuditType) Then
varWhere = (varWhere + " AND ") & "([audittype] IS NULL OR " _
& "[audittype] = " & Quotes(Me.cboAuditType) &
")"
End If

strSQL = "SELECT tbl_issues_log.* " & _
"FROM tbl_issues_log " & _
("WHERE " + varWhere) & _
" ORDER BY tbl_issues_log.issuedescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
' DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing
Debug.Print strSQL

End Sub