[AccessD] Join expression not supported

I had something similar last week...and it turned out to be a references 
issue.  The functions I was using would not allow the query to run.  Mine 
was the DATE() function in the query.  I removed all references...and then 
'selected' them again(note: nothing showed missing...just query would not 
run).  I then compiled and the query ran fine.

My error was very similar, and it was the functions in the SQL that was my 
thorn, not the SQL or the joins/criteria...worth a look???


Thanks,

Mark A. Matte

>From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Join expression not supported
>Date: Mon, 19 Feb 2007 13:22:15 -0600
>
>The query posted below is from SQL view and unfortunately it gives no
>good error message. I am thinking it is just a little to complex for
>Access to evaluate all at one time. I am going to break it into more
>than one query.
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>artful at rogers.com
>Sent: Monday, February 19, 2007 1:11 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Join expression not supported
>
>Try opening it in SQL view instead of design view. Offhand, I don't see
>the problem, but this will enable you to inspect the syntax closely, and
>running it from there might give you a better error message. I would
>also try creating a query for each table that creates a calculated
>column encompassing your Right() expression, etc. This might be the
>problem. With a calculated column the join would be straightforward and
>more amenable to Access's syntax checker.
>
>Also your WHERE seems unncecessarily complex. I might consider creating
>a calculated column for it as well.
>
>Arthur Fuller
>Technical Writer, Data Modeler, SQL Sensei
>Artful Databases Organization
>www.artfulsoftware.com
>
>
>
>
>----- Original Message ----
>From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
>To: Access Developers discussion and problem solving
><accessd at databaseadvisors.com>
>Sent: Monday, February 19, 2007 1:21:48 PM
>Subject: Re: [AccessD] Join expression not supported
>
>
>No it does not run. Just returns error message.
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
>Sent: Monday, February 19, 2007 11:40 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Join expression not supported
>
>Does it run?  You just can't view a join like this in the query builder
>in Access.
>
>Drew
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
>Sent: Monday, February 19, 2007 11:30 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Join expression not supported
>
>I an getting a join expression not supported error from the following
>query. I am not seeing the problem. Thanks
>
>SELECT dbo_DSS_CompletionMaster.PID,
>Right(ConfigMaster.PID,Len(ConfigMaster.PID)-4) AS WellName
>FROM ConfigMaster, dbo_DSS_CompletionMaster
>LEFT JOIN ConfigMaster ON
>Right([ConfigMaster].[PID],Len([ConfigMaster].[PID])-4))=
>dbo_DSS_CompletionMaster.Well_Number
>WHERE (((ConfigMaster.PID) Like "PAT*") AND
>((Right([ConfigMaster].[PID],Len([ConfigMaster].[PID])-4)) Not Like
>"*GAP")
>GROUP BY dbo_DSS_CompletionMaster.PID,
>Right(ConfigMaster.PID,Len(ConfigMaster.PID)-4);
>
>Chester Kaup
>Engineering Technician
>Kinder Morgan CO2 Company, LLP
>Office (432) 688-3797
>FAX (432) 688-3799
>
>
>No trees were killed in the sending of this message. However a large
>number of electrons were terribly inconvenienced.
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Find what you need at prices you’ll love. Compare products and save at MSN® 
Shopping. 
http://shopping.msn.com/default/shp/?ptnrid=37,ptnrdata=24102&tcode=T001MSN20A0701