DCount issue

[RobWulf]

Okay first a bit on my problem. I have a list of employees that comes in a semicolon seperated list in one field (not my choice and it has to be kept this way), and I have a list of all the employees on another table all nicely seperated out by their name and departments. I want to get a count of every time their name shows up in the list however I can't figure out how to get a count if they are part of a group that worked on something.



My code here gives me a great count if they worked a ticket by themselves, but how do I get a count if they worked with someone else too?

 Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like Combo37.Value")

[AlexHedley]

Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like Combo37.Value")

Try

Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like " & Combo37.Value)

You need the combo value outside the quotes so that the value from the combo is passed the the criteria part

If Combo37.Value = 1 then

Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like 1")

Free Tip
http://www.599cd.com/tips/access/dco...y=AlexForumAFN

[AlexHedley]

You can have multiple criteria at the end too so
A=1 AND B=2

Replace A and B with your Field names and 1 and 2 with your variables.

"Field1=" & var1 & " AND Field2=" & var2

[RobWulf]

I tried

 Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like " & Combo37.Value)

However access didn't like that too much, problem I am having is the names show as Joe Smith;Jack Bower;Tom Hanks. If I want to get just Tom Hanks in the combo box it will give me a 0 count unless his name appears by itself. How do I get the count of the other names?

[AlexHedley]

Have you tried splitting your string of names then adding them to the combo?

Split(strToSplit, delimiter)

Dim a
a = Split("blah; blah; blah", ";")