Simple if statement not evaluating

[zfind]

I've got a very simple if statement set up around a recordset / query displayer. It is meant to check if a certain cbobox is blank. If so, then error and exit the sub, else compile the query and execute. For some reason though, it's acting as if the if statement doesn't exist and runs the query every time (which turns up empty). How can this be happening? Screenshot attached to demonstrate that I'm evaluating the correct value.

Code:

Private Sub cmdView_Click()
If cboRegion.Value = Null Then
    MsgBox "Region must be selected"
    Exit Sub
Else
    Dim sql As String
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim strQryName As String
 
    Set db = CurrentDb
    On Error Resume Next
    db.QueryDefs.Delete "qryTemp"
 
    sql = "SELECT tblCountries.Region, tblRecords.PartnerID, tblPartnersets.ChannelID, tblRecords.Year, tblRecords.WeekID, tblWeeks.Period, tblRecords.Verified, tblRecords.[Invoiced Actual], tblRecords.[Payout Actual] FROM tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnerSetID) ON tblWeeks.WeekID = tblRecords.WeekID WHERE (((tblCountries.Region)='" & cboRegion.Value & "')"
    If cboPartner.Value <> "" Then sql = sql & " AND ((tblRecords.PartnerID)=" & cboPartner.Value & ")"
    If cboChannel.Value <> "" Then sql = sql & " AND ((tblPartnersets.ChannelID)=" & cboChannel.Value & ")"
    If cboYear.Value <> "" Then sql = sql & " AND ((tblRecords.Year)=" & cboYear.Value & ")"
    If cboFromWeek.Value <> "" Then sql = sql & " AND ((tblRecords.WeekID) Between " & cboFromWeek.Value & " And " & cboToWeek.Value & ")"
    If cboFromPeriod.Value <> "" Then sql = sql & " AND ((tblWeeks.Period) Between " & cboFromPeriod.Value & " And " & cboToPeriod.Value & ")"
    sql = sql & ");"
 
    strQryName = "qryTemp"
    Set qdef = db.CreateQueryDef(strQryName)
 
    qdef.sql = sql
    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName, acViewNormal, acReadOnly
    CurrentDb.QueryDefs.Delete (strQryName)
    DoCmd.SetWarnings True
 
    qdef.Close
    db.Close
    Set qdef = Nothing
    Set db = Nothing
End If
End Sub

 

[Rabbie]

cboRegion may not be Null, it may be an empty string ("") or (" ") this would make it behave as you describe.

 

[DCrake]

Try again but use

If Me.CboBox = "" Then
Exit sub
Else
.....
End If

 

[zfind]

Just did as suggested:

cboRegion.value = ""
cboRegion.value = " "
me.cboregion = null
me.cboregion = ""
me.cboregion.value = null
me.cboregion.value = ""

All make no difference. I'm checking the value of the cbo in the code and it's showing as null, so by rights it should enter the first clause of the if and show the msgbox.

Could it possible be because I'm using queries and record sets here? I've never used them before and this has never happened before.

EDIT: Hrmm, I swapped the msgbox and query codes around, and it then only showed the msgbox, so no matter what, it's always hitting whatever is in the else area.

 

[DCrake]

Three other alternatives are

If Trim(Me.CboRegion & " ") <> "" Then

Or

If Nz(Me.CboRegion,"") <> "" Then

Or

Add "Not Selected" to the combo List

If Me.CboRegion <> "Not Selected" Then

 

[zfind]

Good old Nz seems to have done the trick. Thanks!

Access is strange animal.