error while executing query in access

Question

Getting error while executing following query in access 2007, how to solve this?

my query is :

SELECT
          A.PRODUCT
        , A.DISPBANK
        , COUNT(*) AS RecCount
        , SUM(Amt) AS TotAmt
FROM
          CBWCFAPENDINGPAYMENTDATA A
WHERE
          A.MATCH     ='Y'
          AND A.ID LIKE'*(SELECT [CASHIN_ID] FROM CBWCFAMISUPLOAD WHERE VENDOR='BRINKS' AND NZ(MATCH,'')='Y')*'
GROUP BY
          A.PRODUCT
        , A.DISPBANK

Can you give exmple of data in your tables, and explain what results you are trying to return please? — jimmy8ball
– jimmy8ball, Commented Apr 28, 2017 at 10:39
There's no space between Like and ' - LIKE'*(SELECT ..... Also - can you use a Select statement like that within a Like (asking as not sure)? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Apr 28, 2017 at 10:41
i'm trying to return distinct columns data from CBWCFAPENDINGPAYMENTDATA table where MATCH column should be 'Y' and id comes from CBWCFAMISUPLOAD table they may be single id or multiple id thats y place like there — vivek padelkar
– vivek padelkar, Commented Apr 28, 2017 at 10:43
ok in that case yes you can check for the string in your like clause, but you need to use double quotes not single in access, this will be the issue — jimmy8ball
– jimmy8ball, Commented Apr 28, 2017 at 10:44

Andre · Accepted Answer · 2017-04-28 11:29:54Z

1

You can't have a subquery inside the LIKE string.

You could use DLookup and string concatenation for this.

AND A.ID LIKE '*' & 
  DLookup("[CASHIN_ID]", "CBWCFAMISUPLOAD", "VENDOR='BRINKS' AND NZ(MATCH,'')='Y'" ) & '*'

answered Apr 28, 2017 at 11:29

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jimmy8ball · Accepted Answer · 2017-04-28 12:47:15Z

1

Ok so your answer should be like the below, and will calculate what you need if the tables have a one to many relationship, and returns all rows from table a where the id is found in table b:

     SELECT A.PRODUCT, A.DISPBANK,Count(*) as RecCount,Sum(Amt) as TotAmt FROM CBWCFAPENDINGPAYMENTDATA A WHERE A.MATCH="Y" AND Format(CStr([A.ID])) IN (SELECT [CASHIN_ID] FROM CBWCFAMISUPLOAD WHERE VENDOR="BRINKS" AND NZ(MATCH,"")="Y")) GROUP BY A.PRODUCT, A.DISPBANK

answered Apr 28, 2017 at 12:47

jimmy8ball

7566 silver badges16 bronze badges

Collectives™ on Stack Overflow

error while executing query in access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related