Solved

Access: LEFT JOIN error

Posted on 2009-06-29
4
234 Views
Last Modified: 2012-05-07
Hi x-perts,

I cannot figure out a syntax error (missing operator) on the second LEFT JOIN

what is wrong here?

Thanks
SELECT Funds.[Fund ID], Funds.[Fund Name], MID(Funds.[Fund Name], 1, 1) As F FROM Funds 
LEFT JOIN Quant1 ON (Quant1.[Fund ID] = Funds.[Fund ID]) 
LEFT JOIN Betas ON (Quant1.[Fund ID]=Betas.[Fund ID]) 
WHERE Funds.[open To Investment] = 'Yes' AND Betas.[MSCI in $] BETWEEN -0.08 AND 0.04

Open in new window

0
Comment
Question by:andy7789
  • 3
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24734538
please try this (adding a couple of brackets)
SELECT Funds.[Fund ID], Funds.[Fund Name], MID(Funds.[Fund Name], 1, 1) As F 
FROM (Funds LEFT JOIN Quant1 ON (Quant1.[Fund ID] = Funds.[Fund ID])  )
LEFT JOIN Betas ON (Quant1.[Fund ID]=Betas.[Fund ID]) 
WHERE (Funds.[open To Investment] = 'Yes' AND Betas.[MSCI in $] BETWEEN -0.08 AND 0.04))

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24734540
sorry, my post had a )) at the end, should have been )
SELECT Funds.[Fund ID], Funds.[Fund Name], MID(Funds.[Fund Name], 1, 1) As F 
FROM (Funds LEFT JOIN Quant1 ON (Quant1.[Fund ID] = Funds.[Fund ID])  )
LEFT JOIN Betas ON (Quant1.[Fund ID]=Betas.[Fund ID]) 
WHERE (Funds.[open To Investment] = 'Yes' AND Betas.[MSCI in $] BETWEEN -0.08 AND 0.04)

Open in new window

0
 

Author Comment

by:andy7789
ID: 24734647
Thank you! It works fine, though I cannot understand the error...  I have seen a lot of references with plain

LEFT JOIN Quant1 ON (Quant1.[Fund ID] = Funds.[Fund ID])
LEFT JOIN Betas ON (Quant1.[Fund ID]=Betas.[Fund ID])

etc in a row
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24734705
ms access is picky about the brackets...
ms sql server does not require any brackets in that context.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question