VBA Syntex for Like in DCount

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 & ""*""", ""
Ernest GroggAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)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)


.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ernest GroggAuthor Commented:
hmmm...it tells me that I am missing an operator in the expression 'Like '*mysearchstring*'
0
 
Ernest GroggAuthor Commented:
sorry i didn't see the second post...let me see this...
0
 
Ernest GroggAuthor Commented:
yeah in the Like portion.
0
 
Rey Obrero (Capricorn1)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 & "*'")
0
 
mbizupCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Good gosh....looks like I need another cup of coffee; totally missed the field not being there!

Jim.
0
 
Ernest GroggAuthor 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!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No, there is no easy way to do that.  With the criteria argument, you must  name the fields individually.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.