trying to use conditional formatting to indicate where field content is unique

[cherold]

I have a subform that displays a list of song titles linked to a particular album id. Some songs exist on more than one album, and I wanted to create a conditional format that would color any song that exists on multiple albums. I made a few attempts but so far nothing works. One thing that doesn't work, for example, is:

("SELECT Count(LPContents.song_id) FROM LPContents where LPContents.song_id = " & [SongID])>1

I don't know if this is close but I just have syntactical issues or whether this is completely improper and useless. It was just my best guess as something that seemed like it might work.

Info on underlying structure: The song titles and song ids are in Songs, the list of albums is in LPs, and LPContents has a key to match each id in Songs to the id of a particular album in LPs. So I can create a list of titles with the query:

SELECT Songs.Title, Songs.SongID, LPContents.LP_id
FROM LPContents INNER JOIN Songs ON LPContents.song_id = Songs.SongID
WHERE ((([LPs].[LPID])=[LPContents].[LP_id]))
ORDER BY LPContents.Side, LPContents.Track, Songs.SongID;

[ranman256]

your query should show the count of the song in other albums
select song, count (Song) as CountOfSong from table

then the condit.formating will change color for count = 1

[Micron]

was the error something about not being able to have an aggregate function in a WHERE clause? If the first suggestion doesn't work for you, you might try using the HAVING keyword in your sql:

("SELECT Count([song_id]) AS CntLPC FROM LPContents WHERE [song_id] = " & [SongID] & " HAVING Count(song_id) >1")

Assumes the ID is a number.

You can also assign the name of a UDF (user defined function - not a sub) to conditional formatting expression.

[cherold]

I didn't quite get what I needed (or just didn't understand) from the replies, but while fooling around I found an alternative that works:

DCount("[LPContents.song_id]","LPContents","LPContents.song_id = " & [SongID])>1

For some reason when I save this I get an error message, but it still works so far.

[June7]

Error maybe because of the [ ] on the field name, should be [LPContents].[song_id] or just [song_id] or even song_id because there are no spaces nor special characters.

Can simplify:

DCount("*", "LPContents", "song_id = " & [SongID])>1