Results 1 to 4 of 4
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Null or Empty for Date Select Textbox

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can't compare anything to Null: =Null won't work because Null doesn't have anything - can't compare an unknown to anything, even another unknown. Not in VBA - other languages might handle this.

    Use IsNull() function in your code.

    http://allenbrowne.com/casu-12.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @dccjr3927

    This does not do what you think.
    Code:
    Dim dt1, dt2 As Date
    With Access vba you must explicitly declare/DIM each variable' data type
    -OTHERWISE they default to variant.
    In this example dt2 will be a DATE data type, but dt1 will be variant.
    You need
    Dim dt1 as Date, dt2 as Date OR
    Dim dt1 as Date
    Dim dt2 as Date

    As June said you can use the intrinsic IsNull function.
    There is also an IsDate function that might apply.
    Good luck

  4. #4
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thank you for all the assistance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-11-2017, 09:48 PM
  2. Clearing a textbox if its already empty
    By sevanty7 in forum Access
    Replies: 3
    Last Post: 05-17-2013, 11:27 AM
  3. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  4. Empty textbox crash
    By deepin125 in forum SQL Server
    Replies: 6
    Last Post: 04-03-2012, 02:55 PM
  5. NULL vs. Empty String
    By obiron in forum Access
    Replies: 3
    Last Post: 08-02-2011, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums