Report Options

G

Guest

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
 
D

Douglas J. Steele

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

(no private e-mails, please)


chickalina said:
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
Click to expand...
 
G

Guest

Thanks Douglas! Perfect!

Douglas J. Steele said:
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

(no private e-mails, please)


chickalina said:
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
Click to expand...
Click to expand...
 
G

Guest

using the word quotes and not "??

Douglas J. Steele said:
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

(no private e-mails, please)


chickalina said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

(no private e-mails, please)


chickalina said:
using the word quotes and not "??

Douglas J. Steele said:
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

(no private e-mails, please)


chickalina said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-Select List 10
multi-search criteria form 3
Run-Time 2580 error 0
Help with WHERE filter on opening report from dialog 4
Help Coding with Search Form 5
Help With Search Form 1
Update unbound TextBoxes on Form; Need to Read AND Write 1
search form 6

Top