Runtime Error 3464 - Data Type Mismatch in Criteria Expression

[maff811]

Hi,

My name is Matt from Perth, Western Australia and I am new to this forum, but have used it many times to seek out advice. Hopefully, someone can point me in the right direction here.

I am using Access 2010 and have setup a Navigation Form (named NavigationForm) with a number of Navigation Subforms (in this instance called Reports). The Navigation Subform (Reports) is a form which allows users to enter parameters to execute queries and display reports. I also have a button that clears all fields. I have used some VBA code to require a user to enter a Start Date, End Date and another parameter from a combo box before they can execute the query.

I have found that when I open the form for the first time, everything runs as expected - i.e. error messages appear if parameters are left blank, or the report is displayed if all parameters are entered.

However, when I click the Clear button, this triggers a Run-Time Error 3464.

If I try and debug the error, I am taken to a DCount line in my code which provides the user with a message to inform them that no records were found, subject to the parameters entered.

If I navigate to another subform and return, everything works fine again. I am thinking that I need some sort of 'Refresh' of the Report subform once I click the Clear button?

The Code is below:

Private Sub ReportsClear_Click()
Me!CasesClosedStartDate = ""
Me!CasesClosedEndDate = ""
Me!CasesClosedLGA = ""
Me!CasesOpenFollowUpStartDate = ""
Me!CasesOpenFollowUpEndDate = ""
Me!CasesOpenFollowUpLGA = ""
Me!CasesLocationStartDate = ""
Me!CasesLocationEndDate = ""
Me!CasesLocationLocation = ""
Me!CasesLGAStartDate = ""
Me!CasesLGAEndDate = ""
Me!CasesLGALGA = ""
Me!CasesCaseManagerStartDate = ""
Me!CasesCaseManagerEndDate = ""
Me!CasesCaseManagerStaffMember = ""
End Sub

Private Sub CasesClosed_Click()
strStartDate = Me.CasesClosedStartDate
strEndDate = Me.CasesClosedEndDate
strLGA = Me.CasesClosedLGA

If IsNull(strStartDate) = True Then
MsgBox "You must enter a Start Date before running this report.", vbCritical
Me.CasesClosedStartDate.SetFocus

Else
If IsNull(strEndDate) = True Then
MsgBox "You must enter an End Date before running this report.", vbCritical
Me.CasesClosedEndDate.SetFocus

Else
If strStartDate > strEndDate Then
MsgBox "You have entered an End Date that is before the Start Date." + vbCrLf + vbCrLf + "Please amend the search dates and try again.", vbCritical
Me.CasesClosedEndDate.SetFocus

Else
If IsNull(strLGA) = True Then
MsgBox "You must select a Local Government Area before running this report.", vbCritical
Me.CasesClosedLGA.SetFocus

Else
If DCount("*", "Cases Closed by LGA") = 0 Then
MsgBox "The search criteria entered located no matching records."

Else
DoCmd.OpenReport "Cases Closed by LGA", acViewPreview, , , acWindowNormal

End If

End If

End If

End If

End If
End Sub

 

[Cronk]

Hi Matt,

First of all, if you are going to post blocks of code, highlight it and click on the # symbol in the top formatting line. That will preserve indents.

When you clear the controls by setting them to "", this is not a Null, it's a ZLS (zero length string). So IsNull("") will return a false.

So either set the controls to Null or, as I do, test for zero length, eg

If len(me.ControlName & "") <1 then

This will give a true whether the value is Null or ""

 

[maff811]

Hi Cronk,

Thanks for your advice. I went down the path of setting each control to Null instead of "" and it worked a treat. Looking at it now, it seemed such a simple fix and almost embarassing to post for help!

Thanks also for letting me know how to post code on this forum.

Regards,
Matt

 

[Cronk]

I expect we all have our 'duh' moments. I know I do from time to time.