SQL/DLookup/Pulling the same field multiple times

[BDarden]

Hello All,



I have the following expression: January: FormatPercent(DLookUp("[EOM]","OpsFeeDataQuery","[Audit Month]=1" And "AGENTID=[Enter AgentID]"),0)

That I can not get to work it seems the best route for this would be to go with SQL which I thought would be this:

SELECT EOM FROM OpsFeeData WHERE AgentID=[Enter AgentID] and AuditMonth=1 AS January

But I get error syntax/missing operator error message...

My entire query thus far looks like this:

PARAMETERS [Enter Audit Year] Short, [Enter Agent ID] Short;
SELECT EOM FROM OpsFeeData WHERE AgentID=[Enter AgentID] and AuditMonth=1 AS January
FROM OpsFeeData INNER JOIN (((AgentData INNER JOIN AuditData ON AgentData.AGENTID = AuditData.AgentID) INNER JOIN OperationsAuditData ON AgentData.AGENTID = OperationsAuditData.AGENTID) INNER JOIN OpsAgentIdData ON AgentData.AGENTID = OpsAgentIdData.AGENTID) ON OpsFeeData.[Agent Name] = OpsAgentIdData.FEEREPORTID
GROUP BY OpsAgentIdData.AGENTID, AgentData.AGENTNAME, FormatPercent(DLookUp("[%EOM]","OpsFeeDataQuery","[Audit Month]=1" And "AGENTID=[Enter AgentID]"),0)
HAVING (((OpsAgentIdData.AGENTID)=[Enter Agent ID]));

I need the query to have a field for each month (Jan, Feb, Mar, etc.) that pulls this info based on the parameters I have included a copy of my db also QA Database.zip

[mrojas]

Trying setting "Unique Values" property to Yes.

Insert DISTINCT between SELECT and EOM

[June7]

Why didn't the DLookup work? What happened?

Try:

January: FormatPercent(DLookUp("[EOM]","OpsFeeDataQuery","[Audit Month]=1 And AGENTID=" & [Enter AgentID]),0)

I don't see an OpsFeeDataQuery object in the db. There are no records and the linked spreadsheets are not available so I cannot test queries.