SQL error - HELP !!

[Viji]

Hi guys,

I have the following rather nasty SQL..it works fine in Access 2000 & gives the results that I want but when I cut and paste the same SQL in VB (to open a recordset) I get a SQL error saying
"Join expression not supported"..Bizzare !!!!

If anyone can have a look @ it I'll appreciate it very much..because I haven't a clue what's causing this ?

Thanks

Here's the SQL

SELECT COMPLEX_DTLS.COMPLEX_NME, PLATFORM_DTLS.PLATFORM_NME, MATBAL_CURRENT_RECONCILED_DATA.VALUE
FROM ((COMPLEX_DTLS INNER JOIN PLATFORM_DTLS ON COMPLEX_DTLS.COMPLEX_INDEX = PLATFORM_DTLS.COMPLEX_INDEX) INNER JOIN TAG_DETAILS ON PLATFORM_DTLS.PLATFORM_INDEX = TAG_DETAILS.PLATFORM_INDEX) INNER JOIN MATBAL_CURRENT_RECONCILED_DATA ON TAG_DETAILS.MAT_BAL_RECON_TAG_NME = MATBAL_CURRENT_RECONCILED_DATA.TAG_ID
WHERE (((COMPLEX_DTLS.FIELD_ID)="CANTARELL") AND ((PLATFORM_DTLS.TYPE)="DRILLING") AND ((PLATFORM_DTLS.REMOTE_SEPERATION)="YES") AND ((TAG_DETAILS.MAPPED_OB_TYP)="EXPORT OIL LINE"));


Cheers
Vijja

[Iouri]

I saw when SQL working in Access gives an error when you call it from VB. Microsoft message can give wrong clue and it has nothing to do with the rreal error. Definetly it is wrong that JOIN is not supported, because you ran it in Access. Try to find error in the syntax.

The only thing wha I can see is that in the end of your SQL
WHERE (((COMPLEX_DTLS.FIELD_ID)="CANTARELL") AND ((PLATFORM_DTLS.TYPE)="DRILLING") AND ((PLATFORM_DTLS.REMOTE_SEPERATION)="YES") AND ((TAG_DETAILS.MAPPED_OB_TYP)="EXPORT OIL LINE"));

Double quotes must be single quotes. Try it.

Iouri Boutchkine
[email protected]

[Viji]

HI,
I have already tried this but it doesn't make any difference..it's the join expression apparently that's causing the problem..the where clause is working fine ?

[Cakkie]

Access starts from the middle, that is, it first select the table in the middle of the join. This is hard to read (sometimes the statement begins with 3 or 4 ('s ), and some providers don't event take this crap (sorry for that word, but that's what it is). Probably part of M$'s 'if-you-can't-read-it-you-can't-change-it-policy'.
Try this:

FROM MATBAL_CURRENT_RECONCILED_DATA MCRD INNER JOIN (
TAG_DETAILS TD INNER JOIN (
PLATFORM_DTLS PD INNER JOIN COMPLEX_DTLS CD
on CD.COMPLEX_INDEX = PDTLS.COMPLEX_INDEX
) on PDTLS.PLATFORM_INDEX = TD.PLATFORM_INDEX
) on TD.MAT_BAL_RECON_TAG_NME = MCRD.TAG_ID



This looks more natural to me, and as far as I can make up, this should be correct. If not, what provider are you using? If you are using Jet, make sure to use Jet 4.0 rather than Jet 3.5

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Viji]

tHANKS FOR UR HELP..i TRIED THIS BUT i'M NOW GETTING A YNTAX ERROR IN THE FROM CLAUSE ?