I am having an issue with whether to use Null or "" for an empty textbox (that has a date picker). When the form opens I have:
Code:
Option Compare Database
Dim dt1, dt2 As Date
Dim strReportSQL As String
Private Sub Form_Load()
With Me.cbxReportType
.AddItem "Defect Reports"
.AddItem "Inventory Reports"
.AddItem "Work Order Reports"
End With
Me.tbxDate1.Value = Null
Me.tbxDate2.Value = Null
Me.cbxReportType.SetFocus
End Sub
Then on a combobox change event it is throwing "Invalid Use of Null". Here is the Change sub:
Code:
Private Sub cbxRptCriteria_Change()
Dim strSQL As String
strSQL = ""
dt1 = Me.tbxDate1.Value
dt2 = Me.tbxDate2.Value
'Creates SQL Query for results box
Select Case Me.cbxReportType.Value
Case "Defect Reports"
Select Case Me.cbxRptCriteria.Value
Case "Category"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Category FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT Category FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Debug.Print strSQL
Case "Defect Type"
If Me.tbxDate1 = "NUll" Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Defect FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT Defect FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "Marketplace"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Marketplace FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT Marketplace FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "Date Range"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
MsgBox "You must select a date range using the Begin Date and End Date fields above.", vbInformation, "ATTENTION"
Else
End If
Case "SKU"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT SKU FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT SKU FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "Employee"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Employee FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT Employee FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
End Select
Case "Inventory Reports"
Me.lstRptSearchResult.ColumnCount = 1
Select Case Me.cbxRptCriteria.Value
Case "All Inventory"
MsgBox "All items in inventory will be included.", vbOKCancel, "All Inventory"
Me.lstRptSearchResult.Enabled = False
Case "Company"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Company FROM Inventory"
Else
strSQL = "SELECT DISTINCT Company FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Debug.Print strSQL
Case "SKU"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT SKU FROM Inventory"
Else
strSQL = "SELECT DISTINCT SKU FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "Discontinued"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Discontinued FROM Inventory"
Else
strSQL = "SELECT DISTINCT Discontinued FROM Inventory WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
End Select
Case "Work Order Reports"
Me.lstRptSearchResult.ColumnCount = 1
Select Case Me.cbxRptCriteria.Value
Case "Date Range"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
MsgBox "You must select a date range using the Begin Date and End Date fields above.", vbInformation, "ATTENTION"
Else
strSQL = "SELECT * FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "Employee"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT Employee FROM WOTracking"
Else
strSQL = "SELECT DISTINCT Employee FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
Case "SKU"
If Me.tbxDate1 = Null Or Me.tbxDate2 = Null Then
strSQL = "SELECT DISTINCT SKU FROM WOTracking"
Else
strSQL = "SELECT DISTINCT SKU FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"""
End If
End Select
End Select
'Populates Listbox
Me.lstRptSearchResult.RowSource = ""
Me.lstRptSearchResult.Value = ""
Me.lstRptSearchResult.RowSource = strSQL
Me.lstRptSearchResult.Requery
End Sub
As a side note, dt1 and dt2 are used in the next report generating sub. Any help would be greatly appreciated.