What are the defaults for the date combos? Try this:

If IsNull(Me.cboStartDate) Then
   stLinkCriteria = stLinkCriteria & ""
Else
   dtStart = Me.cboStartDate
End If
If IsNull(Me.cboEndDate) Then
   If dtStart <> "" Then
      MsgBox "Please select End Date."
      Exit Sub
   End If
Else
   dtEnd = Me.cboEndDate
End If



mmcdonal

Look it up at: http://wrox.books24x7.com

RowSrc: SELECT qryrptAllQuotesByRep.CreatedDate FROM qryrptAllQuotesByRep;
Columns: 1
Bound Col: 1
Enabled: Yes
Locked: No

No Events

Didn't work with the new date code !

Try this then, this is all I can thik of without having the database in front of me:

If dtStart <> "" And dtEnd <> "" Then
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [CreatedDate] BETWEEN " & dtStart & " AND " & dtEnd
   Else
      stLinkCriteria = "[CreatedDate] BETWEEN " & dtStart & " AND " & dtEnd
   End If
End If


Also, add this code BEFORE this section:

Msgbox dtStart & vbCrLf & dtEnd

See what the variables are taking. Then post the results.


mmcdonal

Look it up at: http://wrox.books24x7.com

OK.
If I leave the dates blank I get a type mismatch, If I select both dates, it displays the dates and on ok I get type mismatch.
If I select cboStartDate only I get type mismatch, but if I select cboEndDate only it displays time and date and the the type mismatch.

have you put an input mask on cbostartdate and cboenddate?

Private Sub btnsearchmortginspect_Click()

    On Error GoTo errorhandler

    Dim strSQL As String 'string to store the sql
    Dim varwhere As Variant
    Dim intrcount As Integer

    'user wants to do a search

    strSQL = "INSERT INTO tmpmortginspsearch " & _
        "SELECT tblfileinspect.inspectid, tblfileinspect.arfirm, " & _
        "tblfileinspect.aradviserid, tblfileinspect.clientname1, " & _
        "tblfileinspect.clientname2, tblfileinspect.subdate, tblfileinspect.lender, " & _
        "tblfileinspect.compdate, tblfileinspect.status " & _
        "FROM tblfileinspect WHERE "

    'if there is a submission date
    If Not IsNothing(dtsubdatesearch) Then
        varwhere = "(subdate >= #" & Me.dtsubdatesearch & "#)"
    Else
        varwhere = "(subdate >= date()-180)"
    End If

    'if there is a completion date
    If Not IsNothing(dtcompdatesearch) Then
        varwhere = (varwhere + " AND ") & _
            "(compdate <= #" & Me.dtcompdatesearch & "#)"
    End If

    'if there is an ar firm
    If Not IsNothing(cboarnamesearch) Then
        varwhere = (varwhere + " AND ") & _
            "(arfirm=" & Me.cboarnamesearch & ")"
    End If

    'if there is an adviser
    If Not IsNothing(cboadvisersearch) Then
        varwhere = (varwhere + " AND ") & _
            "(aradviserid=" & Me.cboadvisersearch & ")"
    End If

    'if there is a lender
    If Not IsNothing(cbolendersearch) Then
        varwhere = (varwhere + " AND ") & _
            "(lender like '" & Me.cbolendersearch & "*')"
    End If

    'if there is a client name
    If Not IsNothing(txtclientname) Then
        varwhere = (varwhere + " AND ") & _
            "(clientname1 Like '" & Me.txtclientname & "*') "
        varwhere = (varwhere + " OR ") & _
            "(clientname2 Like '" & Me.txtclientname & "*') "
    End If



There's alot more to the procedure however it works fine for me, allowing for multiple options of search entries. Hope this is of some help to you

Thanks Jeremy, I did have input masks on the source table for cboStartDate and cboEndDate.

I've created a basic test environment without the input masks but am still getting the mismatch.

Thanks for the sample code, I will try to adapt that to my structure and give it a go.


Dave

The key with that sample code is that the tmpmortgsearch table it refers to is a carbon copy of the table that the query is searching.

For some reason access just doesn't like taking only part of the results and inserting into a temporary table