Type mismatch error when searching with dates (1 Viewer)

[MilaK]

Hello,

1. I get type mismatch error at:

strWhere = strWhere & "([run_date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "

if either the start or end dates are left blank. I'm not sure why this is happening. both field types are "Short Date".

2. Also, after I clear "Refresh Button" the search fields on the form, Access is still detecting data and give an error message.

This seems like a simple search form but I'm having lots of problems with in for some reason.

The database is attached.

Thanks,

Dim strWhere As String
strWhere = ""
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim lngLen As Long

   
   If Not IsNull(Me.cmb_project_name.Value) Or Me.cmb_project_name.Value <> "" Then
    strWhere = strWhere & "[run_name] = """ & Me.cmb_project_name & """ AND "
   End If
   
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate.Value) Or Me.txtStartDate.Value <> "" Then
        strWhere = strWhere & "([run_date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
        'strWhere = strWhere & "([run_date] >= " & (Me.txtStartDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate.Value) Or Me.txtEndDate.Value <> "" Then    'Less than the next day.
        strWhere = strWhere & "([run_date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
        'strWhere = strWhere & "([run_date] < " & (Me.txtEndDate + 1) & ") AND "
    End If
    
     'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Please indicate criteria."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
    
        Me.Filter = strWhere
        Me.FilterOn = True
        
    End If

 

[Minty]

Don't use Me.txtStartDate.Value the .Value part when referring to controls it can lead to problems. Value is the default property and for certain controls .Value doesn't return what you think it might.
Avoid referring to it unless you explicitly have to. I'm not sure where the propensity for including it has come from but I see it quite a lot in code posted here.

 

[MilaK]

@Minty,

I've tried it both ways. It still throws an error. The code seems correct to me but yet I get error messages when I run it.

Thanks

 

[Minty]

What if you use CDate() for your date values and IsDate as your check? IsDate returns false for null values

 

[JHB]

There is one main problem in your code, which lead to another one:
1: The Refresh button.
2: The If statement.
When you click the Refresh button, you are clearing the controls using an empty sting like Me.cmb_project_name = ""
When you do that then you can't use If Not IsNull(Me.cmb_project_name) because "" is NOT equal Null.
So the below If statement fails:

   If Not IsNull(Me.cmb_project_name) Or Me.cmb_project_name <> "" Then

So instead of setting Me.cmb_project_name = "" to an empty string you should set it to Null Me.cmb_project_name = Null
Then you can also shorten your if statement to:

 If Not IsNull(Me.cmb_project_name) Then

And all the above applies also for the dates.

 

[Simon_MT]

Perhaps try this it maybe a little longer.

 Function SearchProject() As String
  With CodeContextObject
         If Len(.cmb_project_name) > 0
   SearchProject = '" & .cmb.project_name & "'
          Else
    SearchProject = ""
          End If
  End With
  End Function
   
  Function SearchDateStart() As String
  With CodeContextObject
         If Len(.txtStartDate) > 0
   SearchDateStart = Format(.txtStartDate, "mm/dd/yyyy")
          Else
    SearchDateStart = ""
          End If
  End With
  End Function
   
  Function SearchDateEnd() As String
  With CodeContextObject
         If Len(.txtStartDate) > 0
   SearchDateEnd = Format((.txtEndDate + 1), "mm/dd/yyyy")
          Else
    SearchDateEnd = ""
          End If
  End With
  End Function

All we have done is create variables that can be used elsewhere. You can test for length and construct WHERE clauses or insert these directly into SQL Statements or Form or Report Filters

  Private Function SearchStartCriteria() As String
  Dim StartProject as String
  Dim StartDate as String
   
          If Len(SearchProject) <> 0 Then
                  StartProject = “[run_name] = '" & SearchProject & "'”
          End if
          If Len(SearchDateStart) <> 0 Then
                 StartDate = [run_date] >=#" & SearchDate & "# 
          End If
   
          If Len(SearchProject) =0 And Len(SearchDateStart) = 0 Then
                  MsgBox "No criteria", vbInformation, "Please indicate criteria."
                 SearchStartCriteria = ""
                  Exit Function
          ElseIf Len(SearchProject) <>0 And Len(SearchDateStart) = 0 Then
                  SearchStartCriteria = StartProject 
          Elseif Len(SearchProject) =0 And Len(SearchDateStart) <> 0 Then
                  SearchStartCriteria = StartDate
          Else
              SearchStartCriteria = StartProject & " And " & StartDate
          End if
  End Function

Simon

 

[MilaK]

I've used If

IsDate(Me.txtStartDate) = True

Then
and also

Me.cmb_project_name = Null

to clear the combobox.

Thanks