Runtime error 13 type mismatch (1 Viewer)

mischa

Registered User.
Local time
Today, 14:53
Joined
Jul 25, 2013
Messages
63
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

Registered User.
Local time
Today, 09:53
Joined
Feb 2, 2011
Messages
165
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

AWF VIP
Local time
Today, 07:53
Joined
Jul 2, 2005
Messages
13,826
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

Registered User.
Local time
Today, 14:53
Joined
Jul 25, 2013
Messages
63
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

Registered User.
Local time
Today, 09:53
Joined
Feb 2, 2011
Messages
165
Remove the quotes from DoCmd.OpenForm "frmContacten", , , "strCriteria"
should be
DoCmd.OpenForm "frmContacten", , , strCriteria
 

boblarson

Smeghead
Local time
Today, 06:53
Joined
Jan 12, 2001
Messages
32,059
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.
 

Users who are viewing this thread

Top Bottom