Error in search form

[Soegstad]

I have a search form that enables the user to search the data in a table, and this works fine. But I would like to have the option to search between two dates as well, and this is where the problem lies (mismatch error):

Private Sub cmdSearch_Click()
    'Set the Dimensions of the Module
    Dim strSQL As String, strOrder As String, strWhere As String
    Dim dbNm As Database
    Dim qryDef As QueryDef
    Set dbNm = CurrentDb()

    'Constant Select statement for the RowSource
    strSQL = "SELECT  tblKalkyle.KalkyleID, tblKalkyle.Selger, tblKalkyle.Kundenavn, tblKalkyle.Truck, tblKalkyle.Konsernnavn, tblKalkyle.Dato " & _
    "FROM tblKalkyle"

    strWhere = "WHERE"

    strOrder = "ORDER BY tblKalkyle.KalkyleID;"


    'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
    If Not IsNull(Me.txtID) Then '<--If the textbox txtFName contains no data THEN do nothing
        strWhere = strWhere & " (tblKalkyle.KalkyleID) Like '*" & Me.txtID & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
    End If

    If Not IsNull(Me.txtSøk) Then
        strWhere = strWhere & " (tblKalkyle.Søkekriterier) Like '*" & Me.txtSøk & "*'  AND"
    End If

    If Not IsNull(Me.txtKunde) Then
        strWhere = strWhere & " (tblKalkyle.Kundenavn) Like '*" & Me.txtKunde & "*'  AND"
    End If

    If Not IsNull(Me.txtTruck) Then
        strWhere = strWhere & " (tblKalkyle.Truck) Like '*" & Me.txtTruck & "*'  AND"
    End If


    [COLOR=Red]If Not IsNull(Me.txtDato) Then
        strWhere = strWhere & " (tblKalkyle.Dato) Between '*" & Me.txtDato And Me.txtDato2 & "*'  And"
    End If[/COLOR]

    If Not IsNull(Me.txtKonsern) Then
        strWhere = strWhere & " (tblKalkyle.Konsernnavn) Like '*" & Me.txtKonsern & "*'  AND"
    End If

    If Not IsNull(Me.txtKNummer) Then
        strWhere = strWhere & " (tblKalkyle.Kundenummer) Like '*" & Me.txtKNummer & "*'  AND"
    End If

    If Not IsNull(Me.txtSelger) Then
        strWhere = strWhere & " (tblKalkyle.Selger) Like '*" & Me.txtSelger & "*'  AND"
    End If


    'Remove the last AND from the SQL statment
    strWhere = MID(strWhere, 1, Len(strWhere) - 5)

    'Pass the SQL to the RowSource of the listbox

    Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

The red text is where the problem lies.
I have also tried:

[COLOR=Red]If Not IsNull(Me.txtDato) Then
        strWhere = strWhere & " (tblKalkyle.Dato) Between Like '*" & Me.txtDato And Me.txtDato2 & "*'  And"
    End If[/COLOR]

But this is also coming out as an error??
Thanks alot guys for taking the time
Mads

 

[Mile-O]

If Not IsNull(Me.txtDato) Then
strWhere = strWhere & " (tblKalkyle.Dato) Between '*" & Me.txtDato And Me.txtDato2 & "*' And"
End If

    If Not IsNull(Me.txtDato) Then
        strWhere = strWhere & " (tblKalkyle.Dato) Between #" & CDate(Me.txtDato) & "# And #" & (CDate(Me.txtDato2) + #23:59:59#) & "#  And"
    End If

 

[Soegstad]

Thanks Mile, I don't get the error message anymore, but now the search comes up empty when I run it with dates. Could it be bacuse of the date format? I am using (dd.mm.yyyy).
Mads

 

[Mile-O]

Well, it's best not to bother changing the date's format at table level.

The SQL can return it formatted (see below)

strSQL = "SELECT  tblKalkyle.KalkyleID, tblKalkyle.Selger, tblKalkyle.Kundenavn, tblKalkyle.Truck, tblKalkyle.Konsernnavn, Format(tblKalkyle.Dato, ""dd-mm-yyyy"") " & _
    "FROM tblKalkyle"

 

[Soegstad]

Well, it's not the date format that screws up, I've tried with every possible format now, and it still comes up empty (but only when i try dates?). Maybe I should do the search form the hard way through queries