What is wrong with my query?

[aaronlglover]

I am using toad to query an MDB. When I try to run the below query I get a "Microsoft Access ODBC too few parameters expected 1" error. Anyone have any thoughts?

Thanks

SELECT DISTINCT agtdata5.AGENT_ID, agentA.AGENT_NAME, agentmap2.HIRE_DATE, agtdata5.MU_ID, agtdata5.AGENT_DATA_VALUE, IIf(SchedulesA.STATUS='A','Yes','No') AS 'Attended'
FROM

(select agent3.agent_id, dateserial(mid(agent3.AGENT_DATA_VALUE,2,2),mid(agent3.agent_data_value,4,2),mid(agent3.agent_data_value,6,2)) FROM `\\Wfmid-1350\database$\IEX`.agtdata AS agent3 where agent3.agent_data_id = 2) agentmap2

RIGHT JOIN ((SELECT agent4.AGENT_ID, agent4.AGENT_NAME, Max(agent4.MODIFY_DATE_TIME) AS 'MaxOfMODIFY_DATE_TIME'
FROM `\\Wfmid-1350\database$\IEX`.agent agent4
GROUP BY agent4.AGENT_ID, agent4.AGENT_NAME) AgentA RIGHT JOIN (agtdata as agtdata5 LEFT JOIN (SELECT DISTINCT Schedules.AGENT_ID, Schedules.DETAIL_EXCEPTION, Schedules.STATUS
FROM `\\Wfmid-1350\database$\IEX`.Schedules Schedules
WHERE ((Schedules.DETAIL_EXCEPTION)='VZWTSS-ACSSBill-MiFi')) SchedulesA

ON agtdata5.AGENT_ID = SchedulesA.AGENT_ID) ON agentA.AGENT_ID = agtdata5.AGENT_ID) ON agentmap2.AGENT_ID = agtdata5.AGENT_ID


WHERE (((agtdata5.MU_ID)=1113) AND ((agtdata5.AGENT_DATA_ID)=1))

 

[PHV]

What happens if you launch this code in the SQL view pane of an access query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aaronlglover]

It runs ok when I do it from access (i need to put brackets around the derived tables for access to be cool with it) but it works.

 

[aaronlglover]

Nevermind, that was my problem...removing the brackets in my Toad query. Thanks for responding....

 

[SkipVought]

That error ususally means that one of the fields is not a valid field in your table(s)

Right off I see a problem

dateserial([b]
mid(agent3.AGENT_DATA_VALUE,2,[red]2[/red])[/b]
,mid(agent3.agent_data_value,4,2)
,mid(agent3.agent_data_value,6,2))

The DateSerial function takes 3 arguments: YEAR, MonthNbr and DAY

You need to include the CENTURY, unless you're searching before the tenth century AD. ;-)


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Another strategy: Execute your INNER select select to make sure it works before using it as a FROM source.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]