query won't work

[bringmewater]

Why won't this query work please? It runs fine if I make it a pass
through and supply dates manually.


select v.providerid, v.siteid from
(
select providerid, max(cnt) as cnt from
(
select providerid,siteid,count(*) as cnt from a_tblcards where
visitdate>=[Forms]![frmProviders]![date1] group by providerid,
siteid
) as t
group by providerid
) as u join
(
select providerid,siteid,count(*) as cnt from a_tblcards where
visitdate>=[Forms]![frmProviders]![date1] group by providerid,
siteid
) as v
on (u.providerid=v.providerid and u.cnt=v.cnt)
order by providerid

 

[Van T. Dinh]

"Pass-Through" means that you are using a Back-End different from JET /
Access???

Describe how you run this Query such as via the Access interface (Query
grid)? ADO code? DAO code?

Note that in JET SQL, you cannot use "join" by itself. The linking keywords
in JET SQL are INNER JOIN, LEFT JOIN & RIGHT JOIN but JOIN by itself will
create syntax error.

Check the JET SQL Reference section of Access Help.

 

[bringmewater]

I'm using query grid in sql view.

 

[Van T. Dinh]

Try changing the "join" in

) as u join

to INNER JOIN or LEFT JOIN or RIGHT JOIN.

See Access Help for the meaning of each one above to select a suitable one
for your requirements. My guess is INNER JOIN