iif Statement between dates showing duplicate entries

[p81]

Hey guys, I have a query that needs to list the fund Name if they fall within the authorized start and end date. If it doesn't, the record should still display "None". When I run the following code it displays the information correctly, however I am also getting multiple date of service entries if the client had authorization during different time periods.

Example:

ClientID	dateofservice	Fund	startdate	endate
005435	7/14/2011	MAG	7/1/2011	9/4/2011
005435	7/14/2011	None	9/1/2010	12/1/2010
005435	7/14/2011	None	12/2/2010	3/2/2011
005435	7/14/2011	None	1/12/2011	3/11/2011
005435	7/14/2011	None	3/3/2011	6/3/2011
005435	7/14/2011	None	9/5/2011	12/5/2011

Fund: IIf([dateofservice]>=[qryauthtcm].[startdate] And [dateofservice]<=[qryauthtcm].[endate],[fundname],"None")



Any assistance is greatly appreciated!!




Thanks

[orange]

IIF seems to be working. Perhaps your query is selecting multiple records. Could you post your query and some records?

[p81]

Here is an image of the query.

The records returned are displaying a record for each item that is in the qryauthtcm table. I also need it to display "None" or something else if there are no records in qryauthtcm.

[p81]

I figured it out. The Grouping needs to be changed to Max in order to return a single record. Thanks Orange!!

[orange]

Glad you got it working.