The following SQL statement works in SQL Server 2005. I need to use it in Access 2000 but can't get it working. I used the Query Designer in Access to get the basic tables joined, and then tried adding the AND parts to the SQL View, but get various messages depending where the () are; sometimes I get "JOIN not supported", other times it says it can't represent the query in design view and when I try running it, nothing happens; no errors, no results. It just doesn't do anything.

Does Access support AND/OR criteria in the JOIN/ON clause?

SELECT (columns)
FROM P
INNER JOIN A
ON A.col1 = P.col1
INNER JOIN AI
ON AI.col2 = A.col2
    AND AI.col3 = 1
LEFT JOIN LP
ON LP.col4 = A.col4
    AND LP.col4 = P.col4
    AND LP.col5 = 'text'

Access 2000 only supports ANSI SQL 89, so not as current as SQL Server 2005, which is at least ANSI 92.

Perhaps you can build the query in the designer and create the joins visually, then configure them with the join's properties dialog box.

Did that help?


mmcdonal

The join properties only give three options basically making it a left, right, or inner join. Looks like Access won't be able to do what I need. Thank you anyway.

If I were to make this query into a Stored Procedure or Function in SQL Server, would Access 2000 be able to call it somehow? Access would need to pass a user entered date as a parameter.

I am not sure, but I know that if you make it a View in SQL Server, Access can call it and pass it parameters.


mmcdonal

There are no "parameterized Views"; the closest you get is a Table Valued UDF. If Access can SELECT from a View however, I'd guess it can SELECT from a UDF. I'll play with it. Thanks for the help.