We help IT Professionals succeed at work.

VBA Syntex for Like in DCount

Ernest Grogg
Ernest Grogg asked
on
Hello,

I need some help in the syntex for the following:  

If DCount("*", "MGNameAddressPhone", "Like '*" & Me.txtSearch & "'") = 0 Then

Else

DoCmd.ApplyFilter "", "MGNameAddressPhone", Like ""*"" & me.txtSearch & ""*""", ""
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
That should work fine as long as no one types a ' or " in the txtSearchControl.

Only thing is you might want:

DCount("*", "MGNameAddressPhone", "Like '*" & Me.txtSearch & "*'")

so if the search string appears anywhere in a field, you get a value.

Jim.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
BTW, when your getting syntax errors, a good thing to do is this:

 Dim strWhere as string

 strWhere = "Like '*" & Me.txtSearch & "*'"
 If DCount("*", "MGNameAddressPhone", strWhere)

You can then put a break point on the DCount() and inspect the string that's getting passed before it executes.   Really helps to see the finial result and makes it easy to spot mistakes.

Jim.
Ernest GroggSecurity Management InfoSec

Author

Commented:
hmmm...it tells me that I am missing an operator in the expression 'Like '*mysearchstring*'
Ernest GroggSecurity Management InfoSec

Author

Commented:
sorry i didn't see the second post...let me see this...
Ernest GroggSecurity Management InfoSec

Author

Commented:
yeah in the Like portion.
Top Expert 2016

Commented:
what is the value of "Me.txtSearch"

you need in the criteria section of dcount() the name of the field

"[fieldName] like  '*" & Me.txtSearch & "*'"


DCount("*", "MGNameAddressPhone","[fieldName] like  '*" & Me.txtSearch & "*'")
Most Valuable Expert 2012
Top Expert 2013
Commented:
In addition to Jim's comments, Your criteria is incomplete:

If DCount("*", "MGNameAddressPhone", "Like '*" & Me.txtSearch & "'") = 0 Then


You need to specify the field that you are applying the criteria to.  The syntax should be something like this:

If DCount("*", "MyTableName", "MyFieldName Like '*" & Me.txtSearch & "'") = 0 Then
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Good gosh....looks like I need another cup of coffee; totally missed the field not being there!

Jim.
Ernest GroggSecurity Management InfoSec

Author

Commented:
Ahh...I see..Yeah...is there a way to search every field instead of just one field?  that is what I was trying to accomplish.

Thanks!
Top Expert 2016
Commented:
yes there is..
you have to build your search string using vba.. something like this

dim strSearch as string

strSearch="[Field1] Like '*" & me.txt1 & "' AND [field2] Like '*" & me.text2 & "*'"  

then use it in dcount

DCount("*", "MGNameAddressPhone", strSearch)


.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
No, there is no easy way to do that.  With the criteria argument, you must  name the fields individually.

Jim.