count unique records in a report (1 Viewer)

[jerrywheels]

Hello - simple question; I have a report listing a series of records. One of the fields in the records may contain the same value. I want to create a field (text box) on the form which counts the number of records for which this particular field is unique. For instance, 20 records are in the report within a certain date range for a certain customer. One of the fields is RDSID. RDSID can have the same value for more than one record, but I want to count all records that have the same value in RDSID as one record, and would like to do it in the text box control if I can.
Thanks for the help.
Jerry Bennett
Massachusetts

 

[Bodisathva]

you need to use the distinct keyword, along with a DCount...

DCount("RSID", "tableName", "RSID IN(SELECT DISTINCT RSID 
                                      FROM tableName 
                                      WHERE[I] [additional criteria as needed][/I]")

 

[jerrywheels]

what is wrong with this code?
= DCount("RdSNoRec", "tblInventoryTransactions", "RDSNoRec" IN(SELECT DISTINCT RdSNoRec ) FROM tblInventoryTransactions)
Thanks

 

[ajetrumpet]

= DCount("RdSNoRec", "tblInventoryTransactions", "RDSNoRec" IN(SELECT DISTINCT RdSNoRec ) FROM tblInventoryTransactions)
Thanks

= [color=red]<---is there supposed to be an equal sign here??[/color] DCount("RdSNoRec", "tblInventoryTransactions", "RDSNoRec([color=blue]"[/color])[color=red]<---move quote to end of syntax section[/color] IN(SELECT DISTINCT RdSNoRec )[color=red]<---remove[/color] FROM tblInventoryTransactions)

I would like to see what error message you get with this...

The IN section is the subquery...you don't need () marking with SELECT and FROM clauses here...

 

[Bodisathva]

= DCount("RdSNoRec", "tblInventoryTransactions", "RDSNoRec IN(SELECT DISTINCT RdSNoRec FROM tblInventoryTransactions)")

improper placement of the quotes and the parens...

 

[jerrywheels]

thanks

thanks for replying, you can see that i am virtually inexperienced with code except for what my company paid a consultant to teach me,which funds are now cut off. I cant try this as i am home now but will mon morn and let you know, but in any event, i totally appreciate your help and give you kudos for being here and helping people like me
Best
Jerry Bennett