Search form returning Runtime Error '13', Datatype mismatch

[Eddy Sincere]

I am using a search form which is working kinda strangely. I have two date fields, Start and End. The Start field I want values greater than the date entered and the End field should give values less than the date entered.



When I open the form I get the Run-time Error '13' and when I hit the debug button is highlights the following code (bold & italics):

Private Sub txtFltr_Click()


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


If Not IsNull(Me.txtDtStrt) Then
strWhere = strWhere & "([DtQA] >= " & Format(Me.txtDtStrt, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtDtEnd) Then
strWhere = strWhere & "([DtEnd] < " & Format(Me.txtDtEnd + 1, conJetDate) & ") AND "
End If

My tables and forms are set to Short Date.

Also, what's really odd is if I hit the "Clear" button after I opened the form the search functions work just fine and don't return the Run-Time Error '13'. The dates and other text are filtered just fine.

As always, I'm sure it's something simple that I'm overlooking. Any direction would be much appreciated.

[JamesDeckert]

"conJetDate) &"
do you have a space before your '&'?
You said this happens when you open the form, but the routine depends on a click, I'm confused.
do you know if the first 'if' executed? If so what is contained in strWhere at the time of the error?

[Eddy Sincere]

Yes, there is a space before the "&". Sorry for the confusion. You are correct, the routine depends on a click and only happens when I click the "filter" button. Basically, when I open the form it looks fine. If I enter any information into the search fields I get the run-time error. BUT, if I hit the "Clear" button and then add information to the search fields the form works just fine and does not give me the run-time error. Here's the routine:



Private Sub txtFltr_Click()


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

If Not IsNull(Me.txtPCRNmbr) Then
strWhere = strWhere & "([PCRNmbr] Like ""*" & Me.txtPCRNmbr & "*"") And "
End If

If Not IsNull(Me.cboPrrty) Then
strWhere = strWhere & "([Prrty] Like '" & Me.cboPrrty & "') And "
End If

If Not IsNull(Me.cboCADCtgry) Then
strWhere = strWhere & "([CtgryCAD] Like '" & Me.cboCADCtgry & "') And "
End If

If Not IsNull(Me.cboRqstr) Then
strWhere = strWhere & "([Rqstd] Like '" & Me.cboRqstr & "') And "
End If

If Not IsNull(Me.cboCADStts) Then
strWhere = strWhere & "([CADStts] Like '" & Me.cboCADStts & "') And "
End If

If Not IsNull(Me.txtDys) Then
strWhere = strWhere & "([DysOpn] Like ""*" & Me.txtDys & "*"") And "
End If

If Not IsNull(Me.txtNts) Then
strWhere = strWhere & "([Nt] Like ""*" & Me.txtNts & "*"") And "
End If

If Not IsNull(Me.txtDscrptn) Then
strWhere = strWhere & "([Chng] Like ""*" & Me.txtDscrptn & "*"") And "
End If

If Not IsNull(Me.cboPrcss) Then
strWhere = strWhere & "([SttsPrcss] Like '" & Me.cboPrcss & "') And "
End If

If Not IsNull(Me.txtDtStrt) Then
strWhere = strWhere & "([DtQA] >= " & Format(Me.txtDtStrt, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtDtEnd) Then
strWhere = strWhere & "([DtEnd] < " & Format(Me.txtDtEnd + 1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Duuuude. Like, you didn't even enter filter criteria. Did someone thump your noggin? Enter some words already so I can do my job and get back to the waves.", vbInformation, "Whasuuup, Brah?"
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

[June7]

Which line throws the error? Step debug. Refer to link at bottom of my post for debugging guidelines.

I prefer to use apostrophe instead of doubled quote for text delimiters as shown with PCRNmbr. Is this a text type field?

Parameters for Date/Time type fields need # delimiter, although I've never tried the conJetDate parameter of Format function. I just:

#" & Me.txtDtEnd & "#

Are you sure the comboboxes are returning a text value and not a numeric ID?

[Eddy Sincere]

The line which throws the error is below (italics):

If Not IsNull(Me.txtDtEnd) Then
strWhere = strWhere & "([DtEnd] < " & Format(Me.txtDtEnd + 1, conJetDate) & ") AND "
End If

I changed the routine to match your suggestion (removed ConJet definition and added #) but that same line is still throwing the error. Here's what the date lines look like now:

Private Sub txtFltr_Click()


Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtDtStrt) Then
strWhere = strWhere & "([DtQA] >= #" & Me.txtDtStrt & "#) AND "
End If

If Not IsNull(Me.txtDtEnd) Then
strWhere = strWhere & "([DtEnd] < #" & Me.txtDtEnd + 1 & "#) AND "
End If


Also, I checked the comboboxes and they are pulling two columns, the first is a numeric ID and the second is text. I have only the text column visible and the data is bound to column 2. When I delete the two date fields the search forms works perfectly. It's only when I add the date routines that I get the error.

[June7]

txtDtEnd is a text value. +1 is probably concatenating, not adding. I can reproduce that error with a test in the VBA immediate window:

?"1/1/2015" + 1

Try:

CDate(Me.txtDtEnd) + 1

[Eddy Sincere]

Sorry, but that didn't work. I typed:

strWhere = strWhere & "([DtEnd] < #" & CDate(Me.txtDtEnd) + 1 & "#) And "

I got the same run-time error '13' message.

Also, when I remove both date filters the form works just fine. When I put in the first date filter and eliminate the second one, I get a crazy Run-Time error message '3075' which says:

Syntax error in date in query expression ([PCRNmbr] Like "**") And ([DysOpn] Like "**") And ([Nt] Like "**") And ([Chng] Like "**") And ([SttsPrcss] Like "**") Like 'Implement') And [DtQA] >=##'.

[June7]

Are DtQA and DtEnd native fields in table or are they calculated in query? I have seen this issue with filter criteria applied to date fields that are calculated values.

I have no problem building filter string in VBA with natural date fields.

If you want to provide db for analysis, follow instructions at bottom of my post.

[JamesDeckert]

does it work if you get rid of the '+ 1'? in the sql and add 1 to the date you are typing in on the form?

[JamesDeckert]

The value between the #'s is expected to be a string, not a date, but to do the math you need a date
So I think this should work
strWhere = strWhere & "([DtEnd] < #" & CSTR(CDate(Me.txtDtEnd) + 1) & "#) And "

[Eddy Sincere]

I tried both and neither works. Now I'm getting syntax error messages. Super strange. I reverted back to the original code , zipped the file and attached it. Maybe you can see where I'm messing it up. I haven't split it yet so just open it and hit the "Filter" button on the right hand side. The VBA is tied to the Filter button.

PC03 - Example.zip

[June7]

The form is saved with this string in Filter property:
([PCRNmbr] Like "**") And ([DysOpn] Like "**") And ([Nt] Like "**") And ([Chng] Like "**") And ([SttsPrcss] Like 'Implement') And ([DtQA] >= ##) And ([DtEnd] < ##)

The code builds the string even if I don't enter any data. Your conditional code does not trigger the MsgBox (surfer dude in Nevada desert?) so I get runtime error when code attempts to apply above string to the Filter property. The controls are not Null, they have something in them - an empty string. So change the code to handle both possibilities - Null and empty string:

If Me.txtPCRNmbr & "" <> "" Then

I changed code to use apostrophes instead of doubled quote and the # instead of Format().

All works for me.

What if user selects a DtEnd that is earlier than DtQA?

[Eddy Sincere]

Wow, sounds like there were quite a few changes. Thanks so much for going through it, all the points make sense. You wouldn't have it saved by any chance? (What can I say, I'm desperate).

If the user selects a DtEnd which is earlier than DtQA I don't anything unusual would happen. The dates are fields and do not rely on expressions/calculations so it should just return all records >DtQA and <DtEnd. At least, that's my intent.

Funny that you noticed my error message. I figured getting insulted by a surfer would make it a little more tolerable. :-)

[June7]

Dim strWhere As String
Dim lngLen As Long
    If Me.txtPCRNmbr & "" <> "" Then
        strWhere = strWhere & "([PCRNmbr] Like '*" & Me.txtPCRNmbr & "*') And "
    End If
    If Me.cboPrrty & "" <> "" Then
        strWhere = strWhere & "([Prrty] Like '" & Me.cboPrrty & "') And "
    End If
    If Me.cboCADCtgry & "" <> "" Then
        strWhere = strWhere & "([CtgryCAD] Like '" & Me.cboCADCtgry & "') And "
    End If
    If Me.cboRqstr & "" <> "" Then
        strWhere = strWhere & "([Rqstd] Like '" & Me.cboRqstr & "') And "
    End If
    If Me.cboCADStts & "" <> "" Then
        strWhere = strWhere & "([CADStts] Like '" & Me.cboCADStts & "') And "
    End If
    If Me.txtDys & "" <> "" Then
        strWhere = strWhere & "([DysOpn] Like '*" & Me.txtDys & "*') And "
    End If
     If Me.txtNts & "" <> "" Then
        strWhere = strWhere & "([Nt] Like '*" & Me.txtNts & "*') And "
    End If
     If Me.txtDscrptn & "" <> "" Then
        strWhere = strWhere & "([Chng] Like '*" & Me.txtDscrptn & "*') And "
    End If
    If Me.cboPrcss & "" <> "" Then
        strWhere = strWhere & "([SttsPrcss] Like '" & Me.cboPrcss & "') And "
    End If
    If Me.txtDtStrt & "" <> "" Then
        strWhere = strWhere & "([DtQA] >= #" & Me.txtDtStrt & "#) AND "
    End If
    If Me.txtDtEnd & "" <> "" Then
        strWhere = strWhere & "([DtEnd] < #" & Me.txtDtEnd & "#) AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Duuuude.  Like, you didn't even enter filter criteria.  Did someone thump your noggin? Enter some words already so I can do my job and get back to the waves.", vbInformation, "Whasuuup, Brah?"
    Else
        strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
  End If

A record has DtQA of 5/13/2015 and DtEnd of 5/21/2015.

Criteria entered is:

DtQA >= 5/12/2015 AND DtEnd < 4/9/2015

That record would not be retrieved. Is that what you would expect?


I just noticed a record with DtEnd that is earlier than DtQA for -15 days - is that valid? What is DtQA for?

[Eddy Sincere]

It works perfectly! Again, thanks for the effort, you're a life saver.

Regarding the questions:

DtQA >= 5/12/2015 AND DtEnd < 4/9/2015

That record would not be retrieved. Is that what you would expect?

Yes, I would expect it wouldn't be retrieved because, technically, it should never happen. The DtQA is the "Start Date" and the DtEnd is when it closes. Therefore, DtEnd should never be before DtQA.

No, the record with -15 days isn't valid because the DtEnd should never be before DtQA. I probably fat-fingered the date when doing my mass entries.

Other than that does it look alright?