DCount on multivalue fields

[Dan]

I know using multivalue isn't the best solution but I use them in one case to
assign multiple keywords to observations found during inspections and need to
track the use of those keywords.

What I'm trying to do is count how many records have a KeyWord of "Safety".

I use dcount ("[KeyWords]" , "MyTable" , "[KeyWords].value = 'Safety' ").

If I have one record that contains the KeyWord of Safety and a KeyWord of
Security, it returns a '2'. If I have a record that contains a KeyWord of
Safety and no other KeyWords, it returns a '1'.

For some reason, DCount will return the total number of KeyWords in the sole
(or multiple) individual records that contains the KeyWord of Safety.

Please let me know how to get it to just return the count of records with
the KeyWord of Safety regardless of how many other KeyWords are in the record.

thanks
Dan

 

[fredg]

I know using multivalue isn't the best solution but I use them in one case to
assign multiple keywords to observations found during inspections and need to
track the use of those keywords.

What I'm trying to do is count how many records have a KeyWord of "Safety".

I use dcount ("[KeyWords]" , "MyTable" , "[KeyWords].value = 'Safety' ").

If I have one record that contains the KeyWord of Safety and a KeyWord of
Security, it returns a '2'. If I have a record that contains a KeyWord of
Safety and no other KeyWords, it returns a '1'.

For some reason, DCount will return the total number of KeyWords in the sole
(or multiple) individual records that contains the KeyWord of Safety.

Please let me know how to get it to just return the count of records with
the KeyWord of Safety regardless of how many other KeyWords are in the record.

thanks
Dan

Please always indicate which Access version you are using. It can make
a difference in the answer.

If the field value contains "Safety Security" then it is not
="Safety".

Try:
=DCount("*","MyTable","[Keywords] Like '*Safety*'")

The above should count a 1 for each record that contains the word
"safety" in the Keywords field.

 

[Dan]

I just figured it out. I have to use any other field name in the table
instead of the field name that contains the multivalues. For example: Dcount
( "Location" , "MyTable" , "[KeyWords].value = 'Safety'")