Runtime error 13 type mismatch

[mischa]

I am new to writing vba code and am currently trying to open a form with a couple of criteria that are typed into several boxes. When all given criteria are larger than 0, it should search for those items. I chose deliberately to put it in a openfrm function to try it out.

If IsNull(Naamtabel) = False And IsNull(plaatstabel) = False And IsNull(postcodetabel) = False And IsNull(telefoontabel) = False Then
DoCmd.OpenForm "frmContacten", , , "Naam like '*" + Naamtabel.Value + "*'" _
And "Plaats like '*" + plaatstabel.Value + "*'" _
And "Postcode like '*" + postcodetabel.Value + "*'" _
And "Telefoonnummer like '*" + telefoontabel.Value + "*'"
Else
MsgBox "No criteria to meet"
End If

Access is giving an Runtime error 13 type mismatch. But i know there are items that should appear. When I run the debug, the DoCmd part is enlightened

I hope someone can help me to figure out what's wrong with this.
Thanks in advance!

 

[michaeljryan78]

The "AND" conditions are not in the where condition as it is written above. You may want to try:

dim strCriteria as string
strCriteria = ""
If IsNull(Naamtabel) = False And IsNull(plaatstabel) = False And IsNull(postcodetabel) = False And IsNull(telefoontabel) = False Then
strCriteria = "Naam like '*" & Naamtabel.Value & "*'"
strCriteria = strCriteria & " And Plaats like '*" & plaatstabel.Value & "*'"
strCriteria = strCirteria & " And Postcode like '*" & postcodetabel.Value & "*'"
strCriteria = strCriteria & " And Plaats like '*" & plaatstabel.Value & "*'"
strCriteria = strCirteria & " And Telefoonnummer like '*" & telefoontabel.Value & "*'"
DoCmd.OpenForm "frmContacten", , , strCriteria

Else

MsgBox "No criteria to meet"
End If

 

[RuralGuy]

Put your WHERE CONDITION in a separate string first and use a MsgBox to display it and you will see the problem. FYI: the "&" character is the concatenation character for strings rather than the "+". The "+" can have interesting side effects when used as a concatenation character.

 

[mischa]

Thank you guys for the quick and clear answers!
Access is now asking me to enter a parameter value (regarding to the strCriteria)
When I put the where condition into a messagebox it just gives strCriteria so it does not get the required criteria for some reason. I got now (almost exactly what michealjryan78 gave me):

If IsNull(Naamtabel) = False And IsNull(plaatstabel) = False And IsNull(postcodetabel) = False And IsNull(telefoontabel) = False Then
strCriteria = "Naam like '*" & Naamtabel.Value & "*'"
strCriteria = strCriteria & " And Plaats like '*" & plaatstabel.Value & "*'"
strCriteria = strCriteria & " And Postcode like '*" & postcodetabel.Value & "*'"
strCriteria = strCriteria & " And Plaats like '*" & plaatstabel.Value & "*'"
strCriteria = strCriteria & " And Telefoonnummer like '*" & telefoontabel.Value & "*'"
DoCmd.OpenForm "frmContacten", , , "strCriteria"
Else
MsgBox "No criteria to meet"
End If

I tried several things but I cannot figure out why it does not receive the criteria.
I really hope someone can help me with this.

Thanks in advance!

 

[michaeljryan78]

Remove the quotes from DoCmd.OpenForm "frmContacten", , , "strCriteria"
should be
DoCmd.OpenForm "frmContacten", , , strCriteria

 

[boblarson]

Two things

1. Take the quotes off of strCriteria when opening the form

DoCmd.OpenForm "frmContacten",,, strCritera

2. It looks like you have a Plaats line in your criteria twice.

 

[mischa]

Thanks a lot!