Please could someone help me with an expression I am trying to set up in a report in access?
I am listing all my records, be it on vinyl or CD, and want to list them out alphabetically as seperate files ie. A's, B's etc.
I have set out seperate queries/reports for these and they work perfectly but every record, vinyl and CD, is numbered in the first column which I set up (in design mode) as being =+1 to automatically number them.
What I want to be able to do is list all the records, vinyl and CD, but only consecutively number those that are listed as "Single" in the first part of the "SINGLE/ALBUM" column (ie the vinyl records - see example below). As some are singles only and some are a mixture of singles and/or CD's. I am trying to use the iif/left$ combination to recognise them.
The syntax I am using is:- IIf ( Left$ ([SINGLE/ALBUM], 6) = "Single", =+1, " ") but this doesn't seem to work. I get an error message stating "Syntax error (comma) in query expression". I've been working on this for ages now and it's just about driving me crazy! I just can't find where the problem is.

Could anyone give me some guidance on this

Thanks in advance


No Title Artist Single/Album

1 Name Artist Single
2 Name Artist Single/(CD Name)
Name Artist (CD Name)
3 Name Artist Single/(CD Name)
Name Artist (CD Name)
4 Name Artist Single

You should put this in the control source of your text box and set the running sum property to Over All or Over Group, which ever you prefer.

=IIf(Left$([SINGLE/ALBUM],6)="SINGLE",1,0)

Many thanks RichO. I changed the syntax to your suggestion, set the running sum property to Over Group and changed the format to hide duplicates and it gave me exactly what I wanted.

Thank you for time and suggestion. It's saved me from any more headaches!!

CliveL