MS Access Syntax Error

June 9, 2010 at 11:39:39
Specs: Windows XP

I'm trying to write a query in Microsoft Access that selects multiple pieces of information, but I want one of them to include a where statement. Right now I am getting a syntax error and I can't figure out why. Any suggestions? Here is a simplified example of what I am trying to do:

SELECT car, dog, cat, apple, (select cat from animals where color = 'red')
FROM xTable



See More: MS Access Syntax Error

Report •


#1
June 9, 2010 at 11:59:40

First you do not need a subqury. Second, post the SQL and I will take a look.

Report •

#2
June 9, 2010 at 12:10:45

Thanks, the select in () is what is giving me the problem...I seperated it in the middle.

SELECT 'JE' as JE, 'GL01' as GLID, 'C1' as CID, t.Post_Dt as Date, (SUBSTRING(Acct_ID_Ext, 2, 3) + '-' + SUBSTRING(Acct_ID_Ext, 6, 4)) as Unit, (SUBSTRING(Acct_ID_Ext, 15, 4) +'-'+
SUBSTRING(Acct_ID_Ext, 20, 2)) AS AcctSRev,d.Post_Amt as Amount, 'APPR' AS ApprTag, SUBSTRING(Acct_ID_Ext, 11, 3) as ApprValue, 'APTY' AS AptyTag,

(select Left(RIGHT(RTRIM(i.Itm_Val), 2),1) FROM D03_Trans_Export_For_MV WHERE i.Itm_Val like '*[*]*') As ApprTypeVal,

'IMPT' as IMPTValue, LEFT(a.Acct_ID_Ext, 21) AS Acct_ID_Ext, LEFT(d.Trans_Desc, 29) As Remarks, LEFT(i.Itm_Val, 20) AS Itm_Val, LEFT(d.Trans_Ref1, 10) as Comment, (SELECT Acct_ID_Ext from D03_Trans_Export_For_MV) As BLAH
FROM D03_Trans_Acct t
LEFT OUTER JOIN D03_Acct a ON t.Acct_Type = a.Acct_Type AND t.Acct_No_Int = a.Acct_No_Int
LEFT OUTER JOIN D03_Trans_Det d ON t.Post_Dt = d.Post_Dt AND t.Trans_Type = d.Trans_Type AND t.Btch_No = d.Btch_No AND t.Btch_Itm_No = d.Btch_Itm_No
LEFT OUTER JOIN D03_Trans_Itm i ON t.Post_Dt = i.Post_Dt AND t.Trans_Type = i.Trans_Type AND t.Btch_No = i.Btch_No AND t.Btch_Itm_No = i.Btch_Itm_No
WHERE t.Post_Dt = '2009-07-01'
AND (SUBSTRING(a.Acct_ID_Ext, 2, 3) = '747' OR SUBSTRING(a.Acct_ID_Ext, 2, 3) = '751')


Report •

#3
June 9, 2010 at 13:29:02

You can try this for your syntax error, but it does not appear that the subquery has a Where clause that will limit to one record. You may need to edit that, but I don't know your data.

(Select Left(RIGHT(RTRIM(i.Itm_Val), 2),1)
FROM D03_Trans_Export_For_MV As NextToLast
WHERE NextToLast.Itm_Val like '*[*]*') As ApprTypeVal


Report •

Related Solutions


Ask Question