CODEWITHSUNDEEP
ms-access
Mikayil Abdullayev
Mikayil Abdullayev

Reputation: 12376

MS ACCESS- Editing and undoing SQL causes error

I have a query (equivalent of View in ORACLE, SQL Server) that I saved in an Access database. Now I need to add another column to my select list. But whenever I change the SQL I get errors. Let's say I remove one comma and then put it back. I get errors like "Syntax error after From","Syntax error in Join operation". So if I change anything and then undo the change I still get errors. How can I avoid this?

Edit:Before editing

SELECT A_B.ALICI, Q.QAINOM, M.TIP, Q.QATARIX, Q.CEMIBORC, R2.ADI AS SAT_NOV, M.TAMADI, M.MARK, SA.MIQDAR, SA.SAQIYM, SA.CEMI, SA.FAIZ, A_B.BORC_SU, A_B.MEBLEG, A_B.FERQ, K_G.CEMI_ODEN, A_B.MOBTEL, SA.NOTE
FROM ([SELECT  Round(Sum([DBKASSA].[MEBLEQ]),3) AS CEMI_ODEN, DBKASSA.KODAL_GT
FROM DBKASSA
WHERE (((DBKASSA.TIP)=1) AND ((DBKASSA.KODAL_GT)=[INBUYERID]) AND ((DBKASSA.TARIX)=[INTILLDATE]))
GROUP BY DBKASSA.KODAL_GT]. AS K_G RIGHT JOIN ([SELECT AL.KODALAN,AL.MOBTEL, IIf([Su_CEMIBORC] Is Null,0,[Su_CEMIBORC]) AS BORC_SU, IIf([Su_MEBLEQ] Is Null,0,[Su_MEBLEQ]) AS MEBLEG, (IIf([Su_MEBLEQ] Is Null,0,[Su_MEBLEQ]))-(IIf([Su_CEMIBORC] Is Null,0,[Su_CEMIBORC])) AS FERQ, AL!OBYEKT & (IIf(AL!NUMAY Is Not Null," - " & AL!NUMAY)) AS ALICI
FROM 
 (
   SELECT DBKASSA.KODAL_GT, Sum(DBKASSA.MEBLEQ) AS Su_MEBLEQ
   FROM DBKASSA
   WHERE (((DBKASSA.TIP)=1) AND ((DBKASSA.KODAL_GT)=[INBUYERID]))
   GROUP BY DBKASSA.KODAL_GT
   ) AS OD_AL 
    RIGHT JOIN (
    (
    SELECT DBQAIME.KODALAN, Sum(DBQAIME.CEMIBORC) AS Su_CEMIBORC
    FROM DBQAIME
    WHERE (((DBQAIME.KODALAN)=[INBUYERID]))
    GROUP BY DBQAIME.KODALAN
    ) AS B_AL 
    RIGHT JOIN DBALAN AS AL ON B_AL.KODALAN = AL.KODALAN) ON OD_AL.KODAL_GT = AL.KODALAN
    WHERE (((AL.KODALAN)=[INBUYERID]))
 ]. AS A_B INNER JOIN DBQAIME AS Q ON A_B.KODALAN = Q.KODALAN) ON K_G.KODAL_GT = A_B.KODALAN) INNER JOIN (DBMAL AS M INNER JOIN (DBSA AS SA INNER JOIN [SELECT R2.KOD, R2.VID, R2.ADI
   FROM DBRAB2 AS R2
   WHERE (((R2.VID)=3))]. AS R2 ON SA.KODEMLNO = R2.KOD) ON M.KODMAL = SA.KODMAL) ON Q.QAINOM = SA.QAINOM
WHERE (((Q.QAINOM)=[INSALEINVOICE]))
ORDER BY M.TIP, M.TAMADI;

After editing;

SELECT A_B.ALICI, Q.QAINOM, M.TIP, Q.QATARIX, Q.CEMIBORC, R2.ADI AS SAT_NOV, M.TAMADI, M.MARK, SA.MIQDAR, SA.SAQIYM, SA.CEMI, SA.FAIZ, A_B.BORC_SU, A_B.MEBLEG, A_B.FERQ, K_G.CEMI_ODEN, A_B.MOBTEL, SA.NOTE
FROM ([SELECT  Round(Sum([DBKASSA].[MEBLEQ]),3) AS CEMI_ODEN, DBKASSA.KODAL_GT
FROM DBKASSA
WHERE (((DBKASSA.TIP)=1) AND ((DBKASSA.KODAL_GT)=[INBUYERID]) AND ((DBKASSA.TARIX)=[INTILLDATE]))
GROUP BY DBKASSA.KODAL_GT]. AS K_G RIGHT JOIN ([SELECT AL.KODALAN,AL.MOBTEL, IIf([Su_CEMIBORC] Is Null,0,[Su_CEMIBORC]) AS BORC_SU, IIf([Su_MEBLEQ] Is Null,0,[Su_MEBLEQ]) AS MEBLEG, (IIf([Su_MEBLEQ] Is Null,0,[Su_MEBLEQ]))-(IIf([Su_CEMIBORC] Is Null,0,[Su_CEMIBORC])) AS FERQ, AL!OBYEKT & (IIf(AL!NUMAY Is Not Null," - " & AL!NUMAY)) AS ALICI
FROM 
 (
   SELECT DBKASSA.KODAL_GT, Sum(DBKASSA.MEBLEQ) AS Su_MEBLEQ
   FROM DBKASSA
   WHERE (((DBKASSA.TIP)=1) AND ((DBKASSA.KODAL_GT)=[INBUYERID]))
   GROUP BY DBKASSA.KODAL_GT
   ) AS OD_AL 
    RIGHT JOIN (
    (
    SELECT DBQAIME.KODALAN, Sum(DBQAIME.CEMIBORC) AS Su_CEMIBORC
    FROM DBQAIME
    WHERE (((DBQAIME.KODALAN)=[INBUYERID]))
    GROUP BY DBQAIME.KODALAN
    ) AS B_AL 
    RIGHT JOIN DBALAN AS AL ON B_AL.KODALAN = AL.KODALAN) ON OD_AL.KODAL_GT = AL.KODALAN
    WHERE (((AL.KODALAN)=[INBUYERID]))
 ]. AS A_B INNER JOIN DBQAIME AS Q ON A_B.KODALAN = Q.KODALAN) ON K_G.KODAL_GT = A_B.KODALAN) INNER JOIN (DBMAL AS M INNER JOIN (DBSA AS SA INNER JOIN [SELECT R2.KOD, R2.VID, R2.ADI
   FROM DBRAB2 AS R2
   WHERE (((R2.VID)=3))]. AS R2 ON SA.KODEMLNO = R2.KOD) ON M.KODMAL = SA.KODMAL) ON Q.QAINOM = SA.QAINOM
WHERE (((Q.QAINOM)=[INSALEINVOICE]))
ORDER BY M.TIP, M.TAMADI;

By the way, it worked in MS Access 2010. Mine is Access 2003

Upvotes: 2

Views: 94

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

What has happened is that your subquery or derived table is now bracketed like so

[stuff here].

This causes an error when editing.

The easiest thing to do is use notepad or such like, replace []. with () and paste back.

Try:

SELECT A_B.ALICI,
       Q.QAINOM,
       M.TIP,
       Q.QATARIX,
       Q.CEMIBORC,
       R2.ADI AS SAT_NOV,
       M.TAMADI,
       M.MARK,
       SA.MIQDAR,
       SA.SAQIYM,
       SA.CEMI,
       SA.FAIZ,
       A_B.BORC_SU,
       A_B.MEBLEG,
       A_B.FERQ,
       K_G.CEMI_ODEN,
       A_B.MOBTEL,
       SA.NOTE
FROM   ((SELECT Round(SUM([DBKASSA].[MEBLEQ]), 3) AS CEMI_ODEN,
               DBKASSA.KODAL_GT
        FROM   DBKASSA
        WHERE  ( ( ( DBKASSA.TIP ) = 1 )
                 AND ( ( DBKASSA.KODAL_GT ) = [INBUYERID] )
                 AND ( ( DBKASSA.TARIX ) = [INTILLDATE] ) )
        GROUP  BY DBKASSA.KODAL_GT) AS K_G
        RIGHT JOIN ((SELECT AL.KODALAN,
                           AL.MOBTEL,
                           Iif([Su_CEMIBORC] IS NULL, 0, [Su_CEMIBORC])
                                                                AS
                            BORC_SU,
                           Iif([Su_MEBLEQ] IS NULL, 0, [Su_MEBLEQ])
                                                                AS
                            MEBLEG,
                           ( Iif([Su_MEBLEQ] IS NULL, 0, [Su_MEBLEQ]) ) - (
                           Iif([Su_CEMIBORC] IS NULL, 0, [Su_CEMIBORC]) )
                                                                AS
                                                                           FERQ,
                           AL ! OBYEKT & ( Iif(AL ! NUMAY IS NOT NULL,
                                           " - " & AL ! NUMAY) ) AS
                                               ALICI
                    FROM   (SELECT DBKASSA.KODAL_GT,
                                   SUM(DBKASSA.MEBLEQ) AS Su_MEBLEQ
                            FROM   DBKASSA
                            WHERE
                    ( ( ( DBKASSA.TIP ) = 1 )
                      AND ( ( DBKASSA.KODAL_GT ) = [INBUYERID] ) )
                            GROUP  BY DBKASSA.KODAL_GT) AS OD_AL
                           RIGHT JOIN ( (SELECT DBQAIME.KODALAN,
                                              SUM(DBQAIME.CEMIBORC) AS
                                                Su_CEMIBORC
                                       FROM   DBQAIME
                                       WHERE  ((
                                      ( DBQAIME.KODALAN ) = [INBUYERID] ))
                                       GROUP  BY DBQAIME.KODALAN) AS B_AL
                                        RIGHT JOIN DBALAN AS AL
                                          ON B_AL.KODALAN = AL.KODALAN)
                             ON OD_AL.KODAL_GT = AL.KODALAN
                    WHERE  (( ( AL.KODALAN ) = [INBUYERID] ))) AS A_B
                    INNER JOIN DBQAIME AS Q
                      ON A_B.KODALAN = Q.KODALAN)
          ON K_G.KODAL_GT = A_B.KODALAN)
       INNER JOIN (DBMAL AS M
                   INNER JOIN (DBSA AS SA
                               INNER JOIN (SELECT R2.KOD,
                                                  R2.VID,
                                                  R2.ADI
                                           FROM   DBRAB2 AS R2
                                           WHERE  (( ( R2.VID ) = 3 ))) AS R2
                                 ON SA.KODEMLNO = R2.KOD)
                     ON M.KODMAL = SA.KODMAL)
         ON Q.QAINOM = SA.QAINOM
WHERE  (( ( Q.QAINOM ) = [INSALEINVOICE] ))
ORDER  BY M.TIP,
          M.TAMADI;

Upvotes: 1

Related Questions